Get List of Impersonated Rights

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

Get a list of impersonation rights granted at a Server level.

There isn't currently an easy GUI method of viewing these

Code

-- retrieve a list of impersonation rights granted at the server level


select

spe.*

,spr1.name AS ImpersonatingLogin

,spr2.name AS ImpersonatedLogin

from sys.server_permissions spe

inner join sys.server_principals spr1

on spe.grantee_principal_id = spr1.principal_id

inner join sys.server_principals spr2

on spe.grantor_principal_id = spr2.principal_id

where spe.type = 'im'


--GRANT IMPERSONATE ON LOGIN::sa to [testlogin]