SQL Server – Determine AD Groups Logins for a User

active-directorypermissionssql serversql server 2014sql-server-2008-r2

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):

enter image description here

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?

enter image description here

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:

enter image description here

Best Answer

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.

Related Question