SQL Server Central Management Server Security

By: John Sterrett   |   Updated: 2011-06-20   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Central Management Servers

Problem

I have seen several tips on SQL Server Central Management Server and I want to implement the technology for my team. The only thing holding me back is security. Can you explain how security works with Central Management Server? What security access is needed to manage CMS? How about connecting to CMS? How about running a query using CMS? Check out this tip to answer these questions.

Solution

It's always important to understand the security of a new feature before you start using it in production. Today, we are going to walk through a demo to understand the security used behind the Central Management Server.

In this demo we will have CMS configured with five instances named: PBMDEMO, PBMDEMO\EXPRESS, PBMDEMO\SQL2000, PBMDEMO\SQL2008 and PBMDEMO\SQL2005. The basic configuration is shown below. If you need help creating a your Central Management Server look at this tip.

The table below gives you an overview of the security behind the Central Management Server:

Step 1: We will use the "PBMDEMO\PBMUser" Windows Authenticated account for this demo. The PBMUser account currently doesn't have access to CMS as it's not a member of the ServerGroupReaderRole or ServerGroupAdministratorRole database roles. You will see the following error message shown in the screen shot below.

Step 2: In order to give the PBMUser account login access to CMS we need to track down the CMS database roles. To do this right click on the user under Logins node found under the Security node in SSMS and select properties. Next we will select the User Mappings tab. Click on the msdb database and notice that we now see the ServerGroupReaderRole and ServerGroupAdministrator database roles. These two roles are shown in the red box in the next screen shot below.

Step 3: Now we will give the PBMUser access to CMS. This will be done by giving the account access to the ServerGroupReaderRole. If you wanted to make the user an CMS Administrator you could grant him ServerGroupAdministratorRole access.

Step 4: Now that our user has access we will try to connect to CMS and run a query. When you right click on the CMS server and select "new query" you will see that you don't have access to all the servers in this example. In the bottom left corner of the query window you will see that we are only connected to two out of five instances. This is because Windows Authentication is used to connect to the instances of SQL Server and the user does not have access to connect.

Step 5: We will run a very basic query to test the connection and prove that we only are connected to two instances. In this case we will use "SELECT @@VERSION." You will see that the PBMUser account doesn't have access to connect to the SQL2000, SQL2005 and SQL2008 instances. This can be fixed by granting the PBMUser account access to those instances.

Step 6: To complete this example we will grant the user login access and the access needed to run the queries against CMS on the instances where the user doesn't have access. In this example we are going to give datareader access to MSDB because we are going to include more tips later to automate a daily checklist with CMS, T-SQL and PowerShell.

Note - In the real world you only want to grant the access needed for the query you want to execute against your SQL Server farm.

-- Grant PBMUser access to read all tables in MSDB
USE [master]
GO
EXEC master.dbo.sp_grantlogin @loginame = N'PBMDEMO\PBMUser'
EXEC master.dbo.sp_defaultdb @loginame = N'PBMDEMO\PBMUser', 
@defdb = N'tempdb'
EXEC master.dbo.sp_defaultlanguage @loginame = N'PBMDEMO\PBMUser'
GO
USE [tempdb]
GO
EXEC dbo.sp_grantdbaccess @loginame = N'PBMDEMO\PBMUser',
@name_in_db = N'PBMDEMO\PBMUser'
GO
USE [tempdb]
GO
EXEC sp_addrolemember N'db_datareader', N'PBMDEMO\PBMUser'
GO

Step 7: Now we will rerun the exact same query and you should notice that you connect to the instances and see results for every instance.

Next Steps

• As your SQL Server environment grows, consider some of the options available with SQL Server Central Management Servers to help improve your efficiency.
• Check out these related tips:
  • SQL Server Central Management Servers System Tables
  • Execute same SQL Server query across multiple servers at the same time using Central Management Servers
  • SQL Server Security tips

About the author

John Sterrett is a DBA and Software Developer with expertise in data modeling, database design, administration and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips