An AD User is permissioned through which AD groups that are logins on my SQL Server?

This is a useful query to impersonate any windows user and then list the various AD groups that the user is gaining access to your SQL Server through.  Even if the windows user isn’t an explicit login on the SQL Server, the script will return the AD groups that he or she is a member of that allowed access to SQL Server.

DECLARE @LOGINNAME VARCHAR(255)
SELECT @LOGINNAME = ‘domainname\loginname’
EXECUTE AS LOGIN = @LOGINNAME
SELECT distinct token.*,grp.*
FROM sys.login_token token
JOIN sys.server_principals grp
ON token.sid = grp.sid
WHERE token.[type] = ‘WINDOWS GROUP’
AND grp.[type] = ‘G’
order by 3
REVERT

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s