Hacking the Central Management Studio (CMS)

With this post I want to show you how you can make a central management server (CMS) selective for different user accounts so that you can use public and management roles, only showing the servers that are necessary to do your job. This enables you to only maintain one CMS, but show different servers (and even groups) dependent of your role (operator, junior DBA, Sys DBA,…) these roles are only limited by your needs and imagination.
First I looked into Row- and Cell-Level Security

Goal

With this post I want to show you how you can make a central management server (CMS) selective for different user accounts so that you can use public and management roles, only showing the servers that are necessary to do your job. This enables you to only maintain one CMS, but show different servers (and even groups) dependent of your role (operator, junior DBA, Sys DBA,…) these roles are only limited by your needs and imagination.

First I looked into Row- and Cell-Level Security (http://technet.microsoft.com/library/Cc966395 )
And then I tried to implement parts of this technique on the CMS.
Key is that first you have to define the roles needed for this, so for arguments sake let’s create the 3 roles defined in the introduction.

Role based Row-security

• CMS_Management (sys DBA’s)
• CMS_JuniorDBA
• CMS_Operator

First thing to do is to create these database roles

USE [msdb]
GO
CREATE ROLE [CMS_JuniorDBA]
GO
CREATE ROLE [CMS_Operator]
GO
CREATE ROLE [CMS_Management]
GO

These roles are just placeholders, and will only contain logins (AD groups or SQL logins)

Create the CMS groups

For this hack to function correctly you have to create CMS groups that have (the same) meaningful names so we can map these to the database roles that we created in the previous step.
Please note that the server name in a CMS is unique (being a bit creative here by adding ports or DNS aliases for example can go a long way though)

Altering the sysmanagement_shared_registered_servers view

Prerequisites

Stop all recurring agent jobs on our victim… eugh CMS
To be on the same site, make sure that there are no executing jobs on your server, this is especially trough if your CMS is also a central job server MSX. You can do this by using the following statement:
EXEC msdb.dbo.sp_update_job @job_name=‘SQL Agent Job 1’,@enabled = 0
GO
EXEC msdb.dbo.sp_update_job @job_name=‘ SQL Agent Job 2’,@enabled = 0
GO

Preparing the view to be altered

Please note that everything that follows in this post is not supported by Microsoft, and any CU or SP can (and probably will) undo this change. You bare the full responsibility of implementing this change, so please test this before implementing on a production environment.

Stop the SQL Server instance that is hosting your CMS in the SQL Server configuration manager.
Open command prompt as Admin and go to SQL server bin directory
Example: C:\Program Files\Microsoft SQL Server\<Instance Dir> \MSSQL\Binn

Start SQL Server in single user mode. When you start SQL Server in single user mode it’s always better to limit the app that can connect to SQL to prevent other applications or (un)helpful sidekicks to connect first and lock you out.

Example:- sqlservr -m”SQLCMD” -s<instance id>

Open another command prompt window and start SQLCMD with DAC
Command:- sqlcmd -A -S.\ <instance name>

Execute the following commands (line by line)
1> USE msdb
2> Go

1> update sys.sysschobjs set status = 917504 where name =’sysmanagement_shared_registered_servers’
2> GO
3> update sys.sysschobjs set status = 917504 where name =’sysmanagement_shared_server_groups’
4> GO
Exit single user mode (Ctrl-C)
Restart the server using the SQL Server Configuration Manager

Now both views should be visible in MSDB as user views.

Altering the views

We have to be careful here, the query that uses these views when querying the CMS uses a fixed query plan, so the possible alterations are quite limited. We can alter everything we need for this hack to work, but because of this limitation we can only alter the query in such a way that it doesn’t change the execution plan.

The workaround that I used is to use functions in combination with a case structure. Also note the sequence of the CASE structure. The sysadmin role check is explicitly defined on the bottom of the structure (otherwise your junior DBA might end up seeing more than what he can cope with ;-)

Creating a definition Role Function

CREATE FUNCTION [dbo].[sysmanagement_custom_CMS] (@server_group_id int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
declare @return_value int
declare @group_name sysname
declare @parent_id int
declare @parent_name sysname
select

@group_name=name,
@parent_id=parent_id

from msdb.dbo.sysmanagement_shared_server_groups
where server_group_id =@server_group_id
select

@parent_name=name

from msdb.dbo.sysmanagement_shared_server_groups
where server_group_id = @parent_id

select @return_value=case

when IS_ROLEMEMBER(‘CMS_Management’)=1 and @parent_name = ‘Management’ then 1
when IS_ROLEMEMBER(‘CMS_JuniorDBA’)=1 and @parent_name = ‘CMS_JuniorDBA’
and substring(SYSTEM_USER,1,3)=‘DEV’ then 1
— Use this code if you have separate dev/test/prod domains
when IS_ROLEMEMBER(‘CMS_JuniorDBA‘)=1 and @parent_name = ‘Public’ and

substring(SYSTEM_USER,1,3)=‘ONT’

and @group_name=‘SQL – Development’ then 1
— Use this code to select specific groups in the CMS
when IS_ROLEMEMBER(‘CMS_JuniorDBA‘)=1 and @parent_name = ‘Public’ and substring(SYSTEM_USER,1,3)=‘ONT’
and @group_name=‘SQL – Test’ then 1
when IS_ROLEMEMBER(‘CMS_Operator’)=1 and @parent_name = ‘CMS_Operator’
and @group_name=‘SQL – Production’ then 1
when IS_SRVROLEMEMBER(‘sysadmin’)=1 then 1
else 0
end
return @return_value
END

Now you can use this function in the views used by the CMS.

Example

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[sysmanagement_shared_registered_servers]
AS
(
SELECT server_id, server_group_id, name, server_name, description, server_type
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
WHERE [dbo].[sysmanagement_custom_CMS] (server_group_id)=1
)
GO

Ok fill up your CMS, and deposit your server in the correct groups, by doing this they will be displayed if the user has the correct role. You can by using the same code alter the second view so that you can only see the groups you’re allowed to see.