How to find all the logins that can impersonate login X

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    We use impersonation to run procedures so users don't get direct data access.

    I need to find out which logins have access to impersonate "mydomain\ReportAcct"

    Basically everywhere we've setup a securable with impersonation to "mydomain\ReportaAcct"

    I'm looking for some TSQL that will give me this info appreciate any help, thx!

    Edit: Found my answer, see comment.

     

    • This topic was modified 5 months, 3 weeks ago by  AlphaTangoWhiskey. Reason: indicate that i found the answer
  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    Nevermind i got it. The following is what i was looking for:

    SELECT
    sp2.name AS LoginName
    ,sp.permission_name AS PermType
    ,sp3.name AS PermTarget
    FROM sys.server_permissions sp
    LEFT JOIN sys.server_principals sp2
    on sp2.principal_id = sp.grantee_principal_id
    LEFT JOIN sys.server_principals sp3
    on sp3.principal_id = sp.major_id
    WHERE sp.type = 'IM'

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply