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]