what AD groups logins my user belongs to?

Question

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:

Answer 1

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.

Answer 2

To answer your specific question the easiest way I've found to get a list of AD groups a user belongs to (from SQL Server) is to use sys.login_token or sys.user_token.

You will have to use the EXECUTE AS LOGIN = just like you did above but once you are impersonating the login you can query sys.login_token to get a list of groups the login belongs to. This includes any server level roles and all of the AD groups. There is a principal_id column that links to the sys.server_principals system view. It will be filled in for all of the server roles and for an AD groups that have an entry in sys.server_principals.

To get more database specific information you can go to the database you are interested in and use sys.user_token to get a list of roles/AD groups associated with that database. In this case principal_id is associated with sys.database_principals.

Answer 3

If i understood correctly you can get this done with help of xp_logininfo which returns information about Windows users and Windows groups.

Say by simply executing

EXEC xp_logininfo 'mycompany\HThorne'

Else you can use below query which i have been using from my repository, not sure from where i got this , :), but still can be helpful:

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name