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


No comments:
Post a Comment