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 selectspe.*,spr1.name AS ImpersonatingLogin,spr2.name AS ImpersonatedLoginfrom sys.server_permissions speinner join sys.server_principals spr1on spe.grantee_principal_id = spr1.principal_idinner join sys.server_principals spr2on spe.grantor_principal_id = spr2.principal_idwhere spe.type = 'im'--GRANT IMPERSONATE ON LOGIN::sa to [testlogin]