what AD groups logins my user belongs to?

I am not sure if I have chosen the right title for this question. what
I am really after is, given a individual windows AD user, I would like to
find out the list of the windows AD groups (logins) that have access to an
specific database in this server

when I run the following query

select
    name,
    principal_id,
    type,
    type_desc,
    default_schema_name,
    create_date,
    modify_date,
    owning_principal_id,
    sid,
    is_fixed_role
from sys.database_principals

in my server


Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011
00:54:03 Copyright (c) Microsoft Corporation Standard Edition
(64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I get the following results (partial list):


I need to know all the permissions a particular login has. this login
has access to my server/databases through AD groups.


1) what AD groups, from the list above, does my login belong to?

I have been doing this, below,
but I would really like to find out the list of the AD groups (that have access to this server according to the above picture) that this user belongs to.


First I execute as the user in question

EXECUTE AS LOGIN='mycompany\HThorne'

    DECLARE @User VARCHAR(20)
    SELECT @USER = SUBSTRING(SUSER_SNAME(), 
CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))

i make sure that I have the right credentials

SELECT   @USER 
            , SUSER_SNAME()
            ,SYSTEM_USER
            , USER_NAME()
            , CURRENT_USER
            , ORIGINAL_LOGIN()
            , USER
            ,SESSION_USER

I go to the specific database and use the fn_my_permissions - run as
the user in question

use WebDataImportStage
    go
    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
    GO

    REVERT

and that is giving me the result below:

what AD groups, from the list above, does my login belong to?

All you need to do is execute the following command:

EXEC xp_logininfo 'domain\useraccount','all';
GO

If the account does not have access via any group on that server, AND is a legit account in the domain, you will get no records returned. If the user is found to have permissions you can identify the group they have access from by checking the permission path. This will return the group domain\groupname that is giving the domain user access.

StackExchange Database Administrators Q#125504, answer score: 19