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
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.
ReplyDeleteI'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.
ReplyDeleteThe 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.