Determining the Windows Groups for a SQL Server Login

There was a question this morning on the SQL Down Under mailing list about how to determine the Windows groups for a given login.

That's actually easy for a sysadmin login, as they have IMPERSONATE permission for other logins/users.

Here is an example procedure:

image

 

When I execute it on my system this way:

image

It returns the following:

image

Note that the Usage column could also return "DENY ONLY" or "AUTHENTICATOR".

One thought on “Determining the Windows Groups for a SQL Server Login”

  1. Great script !
    You may want to add "and principal_id <> 0" so it filters only the granted windows groups.
    Johan

Leave a Reply

Your email address will not be published. Required fields are marked *