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 =
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

No comments:

Post a Comment