Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query accounts, domain groups, and members who have admin membership.


Query accounts, domain groups, and members who have admin membership.

Author
Message
Eric M Russell
Eric M Russell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 10287
Comments posted to this topic are about the item Query accounts, domain groups, and members who have admin membership.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
markwiddowson
markwiddowson
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 763
Really useful thanks. Was only looking at this myself yesterday but didn't go the extent of group members. I've amended the script slightly for our use to show the is_disabled status of each principal. This way I can also see the status of each sa account for mixed mode instances. I also ran the script in Management Studio 2008 against all registered servers in a local server group to see the full picture across our landscape: http://msdn.microsoft.com/en-us/library/bb964743.aspx
Eric M Russell
Eric M Russell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 10287
mw162 (2/9/2012)
Really useful thanks. Was only looking at this myself yesterday but didn't go the extent of group members. I've amended the script slightly for our use to show the is_disabled status of each principal. This way I can also see the status of each sa account for mixed mode instances. I also ran the script in Management Studio 2008 against all registered servers in a local server group to see the full picture across our landscape: http://msdn.microsoft.com/en-us/library/bb964743.aspx

That's a good point. At one point I had the is_disabled column, but I'll add it back again.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Nadrek
Nadrek
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1534 Visits: 2714
Very useful, though for anyone doing a more comprehensive security audit, I'd also run Vyaskin's code, which I modified only slightly, and which does not list sysadmin roles, but does list a lot of finer-grained information:

-- Original code: http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm
-- Modified to be a more global report, additional QUOTENAME use, OBJECT:: syntax, and optimized hardcoded strings.

SELECT 'USE' + ' ' + QUOTENAME (DB_NAME()) AS '--Database Context'

SELECT 'EXEC sp_addrolemember @rolename = ' + QUOTENAME (USER_NAME (rm.role_principal_id),'''') + ', @membername = ' + QUOTENAME(USER_NAME (rm.member_principal_id)) AS '--Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY USER_NAME (rm.role_principal_id) ASC, USER_NAME (rm.member_principal_id) ASC

-- works on table and column level permissions too
-- EXERCISE FOR THE READER: Column level permissions would be most efficiently granted with a comma separated list of columns for a given table.
-- The current code creates one statement per column, even on the same table!
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' ON OBJECT::' + QUOTENAME (USER_NAME (obj.schema_id)) + '.' + QUOTENAME (obj.name) +
CASE
WHEN cl.column_id IS NULL
THEN ''
ELSE '(' + QUOTENAME (cl.name) + ')'
END + ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
WHERE perm.class <> 3 -- NOT schema class
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC, USER_NAME (obj.schema_id) ASC, obj.name ASC

-- Schema level permissions
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' ON SCHEMA::' + QUOTENAME (sch.name)
+ ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Schema Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.schemas AS sch
ON perm.major_id = sch.[schema_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.class = 3 -- schema class
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC, sch.name ASC

-- Database level permissions.
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC

SELECT 'USE [master];' AS '--Server Level Database Context'

SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Server Level Permissions'
FROM sys.server_permissions AS perm
INNER JOIN sys.server_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC



Eric M Russell
Eric M Russell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 10287
Nadrek (2/9/2012)
Very useful, though for anyone doing a more comprehensive security audit, I'd refer to Vyaskin's code, which I modified only slightly:

The script you posted is great for scripting out database level permissions that have been granted by the SYSADMIN to non-admin users, which would perhaps be a next step.

However, the script is not querying what domain or service accounts are in a server level role (like SYSADMIN), and it's not resolving domain groups into individual member accounts.
For example, who or what in the organization is a SYSADMIN on an instance of SQL Server? Simply querying the sys.database_permissions or sys.server_principals tables within SQL Server won't include that.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Nadrek
Nadrek
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1534 Visits: 2714
Eric M Russell (2/9/2012)
Nadrek (2/9/2012)
Very useful, though for anyone doing a more comprehensive security audit, I'd refer to Vyaskin's code, which I modified only slightly:

The script you posted is great for scripting out database level permissions that have been granted by the SYSADMIN to non-admin users, which would perhaps be a next step.

However, the script is not querying what domain or service accounts are in a server level role (like SYSADMIN), and it's not resolving domain groups into individual member accounts.
For example, who or what in the organization is a SYSADMIN on an instance of SQL Server? Simply querying the sys.database_permissions or sys.server_principals tables within SQL Server won't include that.


Excellent points; I'll edit my post to make it clear that Vyaskin's script gives different, non-overlapping information.
Geoff A
Geoff A
SSC Eights!
SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)

Group: General Forum Members
Points: 908 Visits: 1799
Nice article, but there is an issue with Windows 2008 and xp_logininfo.

Since my domain is all Windows 2008, the column logininfo_note is always either NULL or xp_logininfo returned error 15404.
The error is documented here;

http://matticus-au.blogspot.com/2009/08/windows-2008-and-xplogininfo.html

The workaround is pretty straight forward, but I personally have not tested it.
Eric M Russell
Eric M Russell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 10287
Geoff A (2/9/2012)
Nice article, but there is an issue with Windows 2008 and xp_logininfo.

Since my domain is all Windows 2008, the column logininfo_note is always either NULL or xp_logininfo returned error 15404.
The error is documented here;

http://matticus-au.blogspot.com/2009/08/windows-2008-and-xplogininfo.html

The workaround is pretty straight forward, but I personally have not tested it.


Yes, I too encountered errors resolving some domain groups on some servers. It has to do with parent/child domains, and the organization's network admin would have to move accounts around in Active Directory to really fix it.
I'd appreciate if someone can suggest a workaround that can be implemented just in the script. Perhaps it can be done using an OPENROWSET and an LDAP query rather than just leveraging xp_logininfo, but that would involve some environment specific coding.
http://www.databasejournal.com/features/mssql/article.php/3849891/Query-Active-Directory-Data-from-SQL-Server-using-T-SQL.htm


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Geoff A
Geoff A
SSC Eights!
SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)

Group: General Forum Members
Points: 908 Visits: 1799
in any case, its still a good article. I have already added your script to my tool box. The missing column is not too much of a concern to me.
graymer
graymer
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 287
Good article, wish I came across it a few weeks ago. Definitely will put it in the tool kit.

Cheers.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search