SharePoint has a button to "Check Permissions". Here you can select a user from Active Directory, and it will magically traverse groups and show you all the access the user has on the site based on what groups they are in.

This is a long shot as I've been working with SQL Server for quite a while and have never heard of this, but just in case I missed something...

Is there anything similar in SQL Server? Right now, checking permissions involves looking at the AD groups that have logins on the instance, and going through AD manually to see who is in which group, and then look at database permissions manually. A "Check Permissions" like SharePoint could save a lot of time.

  • You can query AD via SQL Server: blog.sqlauthority.com/2016/03/30/… – Erik Darling May 18 '17 at 13:39
  • Given windows login, do you want to check all it's permissions, or via what AD groups this login can access the server? – sepupic May 18 '17 at 13:46
  • You can use xp_logininfo (docs.microsoft.com/en-us/sql/relational-databases/…) to enumerate AD groups that provide a user with permissions to connect to SQL Server. This will not detail database permissions, though. – Dan Guzman May 18 '17 at 14:11
  • @sepupic Seeing the databases the user is mapped to via groups would be ideal, but just seeing which groups the user is in that also have a login on the instance would be nice. – SomeGuy May 18 '17 at 14:25

just seeing which groups the user is in that also have a login on the instance would be nice

You can accomplish this using following code:

exec xp_logininfo 'dom\acc', 'all';

Or you can impersonate that login and use sys.login_token like this:

exec as login = 'dom\acc';
select distinct name
from sys.login_token
where principal_id > 0
      and type = 'WINDOWS GROUP';
  • Great! I got hung up for a bit by not using 'all', but once I got that in, I was on my way. – SomeGuy May 18 '17 at 15:49

I was able to combine suggestions here with an old script I had lying around to come up with the following, which will show all the groups a user is in, along with the database roles they are mapped to. Note that this is not thoroughly tested, but it gave the results I needed.

DECLARE @LoginName varchar(50)

CREATE TABLE #tmpResults
[account name] sysname,
[type] char(8),
privilege char(9),
[mapped login name] sysname,
[permission path] sysname NULL

INSERT INTO #tmpResults
exec xp_logininfo @LoginName, 'all'

(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

EXEC sp_MSforeachdb

use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT dbname,username, [mapped login name],logintype ,create_date ,modify_date ,
 SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
 FROM @DB_USers user2
 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
 ,1,1,'') AS Permissions_user
 FROM @DB_USers user1 JOIN #tmpResults ON UserName = [Permission Path]
 GROUP BY [mapped login name], dbname,username ,logintype ,create_date ,modify_date
 ORDER BY DBName,username

 DROP TABLE #tmpResults

At this time the only way I know of to do this would be to create a linked server to your Active Directory. Once you do that you can then create a stored procedure to query AD via Openquery.

Create your proc with parameters for specific groups 
CREATE PROC usp_FindADMemembers 
@GroupName varchar(50), 
@OrgUnit varchar(50) 

Declare @sqlcmd varchar(4000)

SET @sqlcmd = 'SELECT LastName, FirstName, DomainName --whatever fields you want
        + '''SELECT GivenName,sAMAccountName  --whatever fields you require
WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''
AND memberOf=''''CN=' + @groupName + ',OU=Groups,OU=' + @OrganizationalUnit
        + ',DC=YOURDOMAIN,DC=YOURDOMAIN'''' ''' + ')'


Hope this helps


Yes, is entirely possible to do it. You must impersonate the user with EXECUTE AS and then check for permissions using HAS_PERMS_BY_NAME or sys.fn_my_permissions.

I would strongly advise you against attempting to traverse AD and/or trying to reverse engineer permissions from sys.dataabse_permissions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.