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

  • Eric M Russell

    SSC Guru

    Points: 125026

    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

  • markwiddowson

    Valued Member

    Points: 55

    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

    SSC Guru

    Points: 125026

    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%5B/quote%5D

    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

  • Nadrek

    SSC-Insane

    Points: 20039

    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

    SSC Guru

    Points: 125026

    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

  • Nadrek

    SSC-Insane

    Points: 20039

    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

    SSChampion

    Points: 11407

    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

    SSC Guru

    Points: 125026

    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

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Geoff A

    SSChampion

    Points: 11407

    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

    SSC Eights!

    Points: 975

    Good article, wish I came across it a few weeks ago. Definitely will put it in the tool kit.

    Cheers.

  • pjs52

    SSC Veteran

    Points: 212

    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

  • Ray Herring

    SSCertifiable

    Points: 5325

    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?

  • Eric M Russell

    SSC Guru

    Points: 125026

    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

  • Ray Herring

    SSCertifiable

    Points: 5325

    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

  • Eric M Russell

    SSC Guru

    Points: 125026

    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 18 total)

You must be logged in to reply to this topic. Login to reply