SQL Clone
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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35224 Visits: 11772
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
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35224 Visits: 11772
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5706 Visits: 2741
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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35224 Visits: 11772
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5706 Visits: 2741
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 1808
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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35224 Visits: 11772
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 1808
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 Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 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