July 22, 2010 at 2:34 am
Hi All,
Currently I have a scenario wherein a user has logged in via the Windows Authentication to the SQL Server 2005 using SQL Server Management Studio. Now the user does to what all database roles(like db_datareader,db_datawriter etc..) he has access to and what all Server Roles he as access to.
(Since there are 2 kinds of roles (1)Database Roles (2) Server Roles )
Now When the user expands the logins folder in the SQL Server Management Studio , he sees several Windows NT Active Directory Security Groups listed over there , but does not see his windows NT login Id listed over there. This means to see that he must be belonging to one of the Active Directory's Security Group. Since Domain\Domain Users is one of the groups listed over there, he would belong to that group and may be one more Security Group. Now is there is any command or way to find out what Active Directory Security Group he belongs to or what database roles and server roles he posses on that SQL Server Instance.
Thanks in Advance
July 22, 2010 at 3:36 am
TO find out Server Role for any user, following query can be used.
SELECT [name],sysadmin,securityadmin,serveradmin, setupadmin,processadmin,diskadmin,
dbcreator,bulkadmin,loginname FROM master..syslogins
WHERE loginname = '(Login)'
( in the output, all the roles with column valus as 1 have that user under group)
July 22, 2010 at 8:47 am
This is a three part process. Let's look at each.
1) Active Directory Group Membership
There are no tools and commands from within SQL Server that do this correctly, especially when nested groups come into play. The best bet here is to pull the user and group information out of Active Directory and traverse the nested groups to find a list of all groups the user is effectively a member of. A script could pull it out of AD and then store it in a SQL Server table. Then you could use a CTE to recursively grab all the groups for that user.
2) Server Roles
Once you have a list of all potential logins, you can determine memberships and individual permissions with the scripts in this article, or derivations of these scripts:
Auditing SQL Server Permissions and Roles
3) Database Roles
I need to get an article written on auditing the database roles, but basically, you can riff off the following script for each of the users. You'll need to map each of the logins to users within the database.
SELECT dp1.name AS 'User', dp2.name AS 'Role'
FROM sys.database_principals dp1
JOIN sys.database_role_members drm
ON dp1.principal_id = drm.member_principal_id
JOIN sys.database_principals dp2
ON dp2.principal_id = drm.role_principal_id;
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply