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.
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 'domacc', 'all';
Or you can impersonate that login and use sys.login_token like this:
exec as login = 'domacc'; select distinct name from sys.login_token where principal_id > 0 and type = 'WINDOWS GROUP'; revert;
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 = 'DOMAINUSERNAME' 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
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
I would strongly advise you against attempting to traverse AD and/or trying to reverse engineer permissions from