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:

When I execute it on my system this way:

It returns the following:

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