Policy Based Management checking membership of roles
Hi *Configure policies to check the membership of the sysadmin and db_owner roles.* Seems such a simple request (end of chapter practice for my MCTS) but I have just spent an hour searching how to achieve this. Does anyone know how to do this or can point me to the correct facet? Many thanks Rob
There is no specific facet for these checks. I think what the MCTS practice is trying to get you to do is create a condition using a query (like they do previously in the book with a condition called 'Tables without primary keys').
I agree with Kev Riley. It must be to try any random policy check. Otherwise, it must have been stated that how this policy should be evaluated? When should the policy fail? There must be details for that. If it is indeed some random policy requirement, then one you can try is to see that there is only one sysadmin i.e. sa. For that you can use something like this SELECT STUFF((SELECT ',' + serverrolemember.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 serverroles.name = 'sysadmin' FOR XML PATH('')),1,1,'') to be evaluated against the value 'sa'
There is a Facet setup for sysadmin role that could be used for what you need, although it is not very intuitive. So I doubt this would be a correct answer on a test, but it might. It took me a bit to [find it]. [Note I would attach an image but for some reason can't do it from work.] In SSMS in 2008 drill into Management\Policy Management\Facets, and open the properties window for "Server Installation Settings". The very last property is "WindowsUsersAndGroupsInSysadminRole". The description of this property states: "Gets the Windows users and groups that are SQL Server system administrators". Now something to test would be how you check for SQL Logins, cause I do believe this will only get Windows but I don't have a system to test this on at the moment. I believe the task is not necessarily to just check who is a member of either role but to verify that no one outside of your base is a member. You know at least a few accounts that should be members (DBAs, service accounts, etc), your policy should verify that they are the only members expected to be there; and if they are not an alert goes out to tell you someone has been added. :
You guys shouldn't need to do custom for this one. Under Database Security facet there's a property called @IsOwnerSysadmin which you can use to evaluate if the database owner is also sysadmin. If the question is asking you to configure several policies, you can evaluate together if need be using CMS. Create another policy using database facet and property of @IsDBOwner. You can relate the two policies together using Category. Using something like EPMFramework (
http://epmframework.codeplex.com) you can evaluate policies together based on categories and/or Registered Server groups in CMS.
Guys thanks for all your posts I have spent most of the day on this, using both facets and custom scripts but I have been unable to get this to work as I interpret the question, which would be to compare a known list of accounts who are authorized to be members of the sysadmin role & a list of accounts who are authorized to be members of db_owners role. My view is it has to be the script route and as my TSQL falls short at this point, I am going to move on today. I did get a policy with a select script to to work on single known account value, I then worked on multiple accounts using array. I was working on 2 policies one comparing a known list of sysadmins and the other Db_owners, if the known values do not match the actual values then the policy fails. Heavy going but very interesting, I'll get there :) might have to park it for a while and come back to it. Just spent a day on a 30min lesson :)