Checking impersonation in SQL Server

Question

I have several DBs with UserA and UserB. In some of those DBs, impersonation has been granted (GRANT IMPERSONATE ON LOGIN::UserA TO UserB), in other DBs users have no impersonation.

How can I check with a query if a user has got the impersonation or not? Does a flag exist in any system table for this?

Answer 1

I found this query...

SELECT  DB_NAME() AS 'database'
        ,pe.permission_name
        ,pe.state_desc
        ,pr.name AS 'grantee'
        ,pr2.name AS 'grantor'
FROM    sys.database_permissions pe
JOIN    sys.database_principals pr
        ON  pe.grantee_principal_id = pr.principal_Id
JOIN    sys.database_principals pr2
        ON  pe.grantor_principal_id = pr2.principal_Id
WHERE   pe.type = 'IM'

Here...

http://tenbulls.co.uk/2012/01/30/what-are-the-impersonation-rights-for-my-database-users/