February 9, 2012 at 12:01 am
Comments posted to this topic are about the item Query accounts, domain groups, and members who have admin membership.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2012 at 3:03 am
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
February 9, 2012 at 8:23 am
mw162 (2/9/2012)
That's a good point. At one point I had the is_disabled column, but I'll add it back again.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2012 at 8:54 am
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
February 9, 2012 at 9:22 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2012 at 9:57 am
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.
February 9, 2012 at 1:35 pm
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.
February 9, 2012 at 2:03 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2012 at 2:07 pm
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.
February 10, 2012 at 10:53 am
Good article, wish I came across it a few weeks ago. Definitely will put it in the tool kit.
Cheers.
February 15, 2012 at 1:47 pm
Very nice query! It's also in my bag of tricks now and actually opened my eyes to who actually has access to instances on a server I inherited. Time to clean house...:-D
February 21, 2012 at 1:28 pm
Hi Eric,
Great tool and very timely for me :).
I wonder about an addition. Of course in SQL 2008 Local Admins are not automatically members of Builtin/Administrators, But it would still be nice to have the Local Admins included in the output. I know they don't have direct access to the database services from inside SQL but they still have a lot of external access and oportunity to affect server performance.
Do you know of a way to query the local machine for the members of Local Administrators?
February 21, 2012 at 2:17 pm
Ray Herring (2/21/2012)
Hi Eric,Great tool and very timely for me :).
I wonder about an addition. Of course in SQL 2008 Local Admins are not automatically members of Builtin/Administrators, But it would still be nice to have the Local Admins included in the output. I know they don't have direct access to the database services from inside SQL but they still have a lot of external access and oportunity to affect server performance.
Do you know of a way to query the local machine for the members of Local Administrators?
Currently, the script won't list out BUILTIN\Administrators members, unless that role itself is a member of a SQL Server admin role. This by itself should give you a list of Local Windows Server Admin members:
xp_logininfo 'BUILTIN\Administrators', 'members';
Because a Windows Server Administrator can stop/restart services, they have the capability to re-start SQL Server in Single-User mode, which would grant them the privillage of the SQL Server SYSADMIN role.
Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out
http://msdn.microsoft.com/en-us/library/dd207004.aspx
You're absolutely right that members of BUILTIN\Administrators should really be added to the result, because they can gain access to a SQL Server instance as SYSADMIN.
So, as we can see, knowing who's a SYSADMIN on your SQL Server instance is not as simple as drilling down on \Security\Server Roles\sysadmin in SSMS. There can be domain groups that allow members to inherit SYSADMIN access, and there are Local Admin groups whose members can gain access as SYSADMIN whenever they choose.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2012 at 2:59 pm
Eric M Russell (2/21/2012)
Ray Herring (2/21/2012)
Hi Eric,Great tool and very timely for me :).
I wonder about an addition. Of course in SQL 2008 Local Admins are not automatically members of Builtin/Administrators, But it would still be nice to have the Local Admins included in the output. I know they don't have direct access to the database services from inside SQL but they still have a lot of external access and oportunity to affect server performance.
Do you know of a way to query the local machine for the members of Local Administrators?
Currently, the script won't list out BUILTIN\Administrators members, unless that role itself is a member of a SQL Server admin role. This by itself should give you a list of Local Windows Server Admin members:
xp_logininfo 'BUILTIN\Administrators', 'members';Because a Windows Server Administrator can stop/restart services, they have the capability to re-start SQL Server in Single-User mode, which would grant them the privillage of the SQL Server SYSADMIN role.
Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out
http://msdn.microsoft.com/en-us/library/dd207004.aspx
You're absolutely right that members of BUILTIN\Administrators should really be added to the result, because they can gain access to a SQL Server instance as SYSADMIN.
So, as we can see, knowing who's a SYSADMIN on your SQL Server instance is not as simple as drilling down on \Security\Server Roles\sysadmin in SSMS. There can be domain groups that allow members to inherit SYSADMIN access, and there are Local Admin groups whose members can gain access as SYSADMIN whenever they choose.
Sorry, I guess I did not provide a good explanation.
The SQL Group "BuiltIn\Administrators" does not exist in SQL 2008. In SQL 2005 machine's Local Administrators group is automatically added to the SQL Group "BuiltIn\Administrators" so by default Local Admins are members of the SysAdmin Fixed Role. During installation SQL 2008 does not create the SQL Group "BuiltIn\Administrators" and by default, Local Administrators are not members of the SysAdmin Fixed Role. The installer actually prompts you to add SysAdmin users.
So on a SQL 2008 server the xp will never return "BuiltIn\Administrators" (unless you create the group yourself:) )
So what I am asking for is a way to get the members of the Windows Server Local Administrators group.
I like the SQL 2008 approach and don't want to automatically add Local Administrators to the SysAdmin Fixed Role. I just want to know who they are 🙂
BTW, I had not considered the single user mode hack you mentioned. That is a sneaky way to get there.
Thanks
Ray
February 21, 2012 at 3:16 pm
Ray Herring (2/21/2012)
Eric M Russell (2/21/2012)
Ray Herring (2/21/2012)
Hi Eric,Great tool and very timely for me :).
I wonder about an addition. Of course in SQL 2008 Local Admins are not automatically members of Builtin/Administrators, But it would still be nice to have the Local Admins included in the output. I know they don't have direct access to the database services from inside SQL but they still have a lot of external access and oportunity to affect server performance.
Do you know of a way to query the local machine for the members of Local Administrators?
Currently, the script won't list out BUILTIN\Administrators members, unless that role itself is a member of a SQL Server admin role. This by itself should give you a list of Local Windows Server Admin members:
xp_logininfo 'BUILTIN\Administrators', 'members';Because a Windows Server Administrator can stop/restart services, they have the capability to re-start SQL Server in Single-User mode, which would grant them the privillage of the SQL Server SYSADMIN role.
Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out
http://msdn.microsoft.com/en-us/library/dd207004.aspx
You're absolutely right that members of BUILTIN\Administrators should really be added to the result, because they can gain access to a SQL Server instance as SYSADMIN.
So, as we can see, knowing who's a SYSADMIN on your SQL Server instance is not as simple as drilling down on \Security\Server Roles\sysadmin in SSMS. There can be domain groups that allow members to inherit SYSADMIN access, and there are Local Admin groups whose members can gain access as SYSADMIN whenever they choose.
Sorry, I guess I did not provide a good explanation.
The SQL Group "BuiltIn\Administrators" does not exist in SQL 2008. In SQL 2005 machine's Local Administrators group is automatically added to the SQL Group "BuiltIn\Administrators" so by default Local Admins are members of the SysAdmin Fixed Role. During installation SQL 2008 does not create the SQL Group "BuiltIn\Administrators" and by default, Local Administrators are not members of the SysAdmin Fixed Role. The installer actually prompts you to add SysAdmin users.
So on a SQL 2008 server the xp will never return "BuiltIn\Administrators" (unless you create the group yourself:) )
So what I am asking for is a way to get the members of the Windows Server Local Administrators group.
I like the SQL 2008 approach and don't want to automatically add Local Administrators to the SysAdmin Fixed Role. I just want to know who they are 🙂
BTW, I had not considered the single user mode hack you mentioned. That is a sneaky way to get there.
Thanks
Ray
There are no "SQL Server Groups"; there are SQL Server roles. Windows domain accounts can be created as login accounts in SQL Server with membership in one or more SQL Server roles. The install process for SQL Server 2000 and 2005 would automatically add a login account for the Windows group BuiltIn\Administrators, and then grant that account membership in SYSADMIN.
By default, SQL Server 2008 will not add a login account for BuiltIn\Administrators. However, a member of BuiltIn\Administrators can still gain access to SQL Server as SYSADMIN by re-starting the service in Single-User mode. That's why it's important to know what domain account are members of BuiltIn\Administrators, even if BuiltIn\Administrators doesn't have a login created in SQL Server. The xp_logininfo procedure doesn't return information on SQL Server role members, but rather Windows group members.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply