Using Policy-based Management to Check Sysadmin Membership

I was sent an email recently from a reader of my simple-talk article Policy-based Management and Central Management Servers asking about the usage of the @WindowsUsersAndGroupsInSysadminRole property to detect the existence of sysadmins outside a known base; in other words, a scheduled check to see if unauthorised users had gained access to the sysadmin role.

As much as I love Policy-based Management, the documentation and error messages leave a little to be desired, so it wasn't immediately obvious how to use this property to achieve the desired result.

Here's the solution...

The trick is using the Array function as shown below. "Field" is simply @WindowsUsersAndGroupsInSysadminRole, but "Value" uses the Array function to convert a CSV list of domain accounts (AD groups or users) into an array data type for comparison with the array data type returned by the @WindowsUsersAndGroupsInSysadminRole function.

On my StrataDB development environment, the results of this policy execution is shown below;

The policy failed because the actual list (array) of sysadmin accounts includes more than the expected list (array).

Hope this helps.

Cheers,
Rod.

Comments

[Colleen]

I don't suppose there's a way to search for a single login within that @WindowsUsersAndGroupsInSysadminRole array, is there?

[Rod Colledge]

Hey Colleen - Do you mean creating a Policy to check if a particular user is a member of the sysadmin group?

[Colleen]

Rod - Yep, that's what I meant. I did find this (http://jonmorisissqlblog.blogspot.com/2011/04/configure-policy-to-checks-that.html) afterwards, but I'd be curious to know if you knew of another way.

[Rod Colledge]

Hey Colleen, and sorry for the late reply! - Yep, that post should cover your need nicely.