Impersonate 되어있는 Login의 Grantor와 Grantee 찾기

Impersonate 되어있는 Login의 Grantor와 Grantee 찾기

/*Search a Grant Impersonate to login*/

/*2014-10-28 Taewwok Cha*/

SELECT SP.grantor_principal_id AS GrantorPrincipalId

, SPR.name AS GrantorLoginName

, SP.grantee_principal_id AS GranteePrincipalId

, SPR2.name AS GranteeLoginName

, SP.class_desc AS PrincipalClassName

, SP.type AS PermissionType

, SP.permission_name AS PermissionName

, CASE

WHEN sp.state='D' THEN 'D = DENY'

WHEN sp.state='R' THEN 'R = REVOKE'

WHEN sp.state='G' THEN 'G = GRANT'

WHEN sp.state='W' THEN 'W = GRANT WITH GRANT OPTION'

END AS PermissionGrantState

, SP.*

  FROM sys.server_permissions AS SP

  JOIN sys.server_principals AS SPR

ON SP.grantor_principal_id = SPR.principal_id

  JOIN sys.server_principals AS SPR2

ON SP.grantee_principal_id = SPR2.principal_id

 WHERE sp.type='IM  '

 ORDER BY

SP.grantor_principal_id 

, SP.grantee_principal_id