4/29/2011

Configure a policy to check that the BUILTIN\Administrators group is in the sysadmin server role

I started looking at how to use policy management to audit server builds.  The first item on my list is to write a policy to make sure the BUILTIN\Administrators group was added to the sysadmin server role.

After a bit of Googling I found this Using Policy-based Management to Check Sysadmin Membership in which Rod Colledge recommends using the @WindowsUsersAndGroupsInSysadminRole.  This is in the Server Installation facet by the way.  I gave it a go, but quickly realized it wouldn't suit my purposes because the array that gets returned is all the users and groups in the sysadmin role.  So, if the check is for a single user like, @WindowsUsersAndGroupsInSysadminRole = Array('builtin\administrators'), it fails.

I'm only interested in verifying that the builtin\administrators are sysadmins, and that's it.

I decided to go the ExecuteSql way and found a way to get the info I was looking from from the system tables. I modified the query as follows:

SELECT serverroles.name
FROM   sys.server_principals AS serverroles
       
JOIN sys.server_role_members serverrolemembers
         
ON serverrolemembers.role_principal_id = serverroles.principal_id
       
JOIN sys.server_principals serverrolemember
         
ON serverrolemembers.member_principal_id =
            
serverrolemember.principal_id
WHERE  serverrolemember.name = 'BUILTIN\Administrators' 

  
I wrapped this query with ExecuteSql and created a condition using it.  (I arbitrarily chose the Server facet.)


I then created a policy which ran the single condition



2 comments:

  1. Excellent post, but I'm hoping you can help me out as I'm trying to implement this. When I attempt to past in the ExecuteSQL(.... command in the field, and set the value to sysadmins, it will not let me save it, and says the property 'expressionNode' is not set. Seems like I can only use the facets that are pre-built in, and nothing else.

    ReplyDelete
  2. I'm not sure how you're going about it, all I can say is what's above worked for me at the time that I did this. Be sure to click the links and read through the references.

    The one thing I learned is that policy-based management only covers a small subset of what we as DBA's would like to do and as a result I do not find it very useful. I've been in dozens of customer environments and have yet to find anyone using policy-based management. My personal opinion is that policy-based management is one of those features that isn't very useful.

    ReplyDelete