See who has access to databases via AD group membership

Question

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.

Answer 1

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';
revert;

Answer 2

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.

-- SHOW PERMISSIONS FOR A USER BASED ON MEMBERSHIP IN AD GROUPS ONLY
DECLARE @LoginName varchar(50)
SET @LoginName = 'DOMAIN\USERNAME'



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'

-------------------
DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
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 ,
 STUFF(
 (
 SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
 FROM @DB_USers user2
 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
 FOR XML PATH('')
 )
 ,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

Answer 3

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.

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

AS
Declare @sqlcmd varchar(4000)

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

EXEC(@sqlcmd)

Hope this helps

Answer 4

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.