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: