We have been able to use Control Server Permission since SQL Server 2005. You can use this permission as an alternative to sysadmin. But it does not mean both are the same. As you know, sysadmin is a super user who can do anything on the sql server.
The differences between sysadmin and control server can be found in the table below. These are the differences I see at the first moment. I’m sure there are other differences. But I could say that it was enough to make me stop.
Differences Between Control server and sysadmin
| Control Server | sysadmin | |
| Can we deny at Instance Level? | Yes | No |
| Can it configure Database Mail? | No | Yes |
| Can it manage Jobs? | No | Yes |
| Can it create Linked Server? | No | Yes |
| Can it impersonate other users with Sysadmin rights? | Yes | Yes |
As you can see, the most important feature of Control Server permission is; It is able to impersonate sysadmins.
You can see the server level rights of a Login that has Control Server permission:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CONNECT SQL SHUTDOWN CREATE ENDPOINT CREATE ANY DATABASE CREATE AVAILABILITY GROUP ALTER ANY LOGIN ALTER ANY CREDENTIAL ALTER ANY ENDPOINT ALTER ANY LINKED SERVER ALTER ANY EXTERNAL DATA SOURCE ALTER ANY EXTERNAL FILE FORMAT ALTER ANY CONNECTION ALTER ANY DATABASE ALTER RESOURCES ALTER SETTINGS ALTER TRACE ALTER ANY AVAILABILITY GROUP ADMINISTER BULK OPERATIONS AUTHENTICATE SERVER EXTERNAL ACCESS ASSEMBLY VIEW ANY DATABASE VIEW ANY DEFINITION VIEW SERVER STATE CREATE DDL EVENT NOTIFICATION CREATE TRACE EVENT NOTIFICATION ALTER ANY EVENT NOTIFICATION ALTER SERVER STATE UNSAFE ASSEMBLY ALTER ANY SERVER AUDIT CREATE SERVER ROLE ALTER ANY SERVER ROLE ALTER ANY EVENT SESSION CONNECT ANY DATABASE IMPERSONATE ANY LOGIN CONTROL SERVER |
Query SQL Server Instance Level Permissions
With the following query, you can see the permissions of the login you are connected to has:
|
1 2 |
SELECT entity_name, permission_name FROM sys.fn_my_permissions(NULL, NULL) |
For a clearer understanding, you must first understand the following permission types, which are announced with SQL Server 2014.
CONNECT ANY DATABASE Permission in SQL Server
If you grant this permission to a login, it can connect to the databases on Instance, but has no authority over the databases. So you can not even select data from tables.
IMPERSONATE ANY LOGIN Permission in SQL Server
If you grant this permission to a login, it may impersonate a sysadmin on Instance. Or vice versa, you can deny
to prevent someone who has CONTROL Server permission from impersonating sysadmin. 🙂
SELECT ALL USER SECURABLES Permission in SQL Server
If you grant this permission to a login, it can access data on any database it can connect to. If you grant SELECT ALL USER SECURABLES and CONNECT ANY DATABASE permission together, it will make sense.
Consider a scenario that includes all of the permissions mentioned above.
Suppose that there’s a new dba at work. And you want this DBA to be able to do management, but you don’t want him/her to see the data.
First, grant CONTROL Server permission to this login.
Then, deny this login to prevent him/her to viewing the data.(DENY SELECT ALL USER SECURABLES)
Then deny this login to prevent him/her to impersonating a sysadmin on Instance.(DENY IMPERSONATE ANY LOGIN)
On the screen that appears, click on the Permission tab and click Search.
On the screen that appears, in the “Select these object roles” section, when “Logins, Server Roles” is selected, click Browse.
On the screen that appears, we find the controlserverLogin and click the box next to it and click ok.
Again we see the previous screen. In this screen, we select controlserverLogin as follows and when controlserverLogin is selected, grant Control server permission to the login (click on the box below Grant). Finally click ok to complete the process.
Capabilities of a Login that has Control Server Permission on the Instance
After granting control server permission to “controlserverLogin” at the instance level as above, we will test what it can do by connecting to the instance with this login.
Can a Login that has Control Server Permission Manage SQL Server Agent?
As you can see first, we cannot see the SQL Server Agent. So a login that has Control Server permission can not manage SQL Server agent.
Can a Login that has Control Server Permission Create a Linked Server?
When we click on the new linked server from Server Object, we receive an error like the following. But we are able to alter an existing linked server.
A required operation could not be completed. You must be a member of the sysadmin role to perform this operation.
Can a login that has Control Server Permission create a login and authorize it in a database?
As you can see in the script below, we are able to create a login and authorize it in a database.
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO CREATE LOGIN [controlservertest] WITH PASSWORD=N'xx', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [AdventureWorks2014] GO CREATE USER [controlservertest] FOR LOGIN [controlservertest] GO GRANT ALTER TO [controlservertest] GO |
If we want to grant db_owner privileges to login using the following script, we receive the error as follows.
|
1 2 3 4 |
USE [AdventureWorks2014] GO ALTER ROLE [db_owner] ADD MEMBER [controlservertest] GO |
Msg 15151, Level 16, State 1, Line 4
Cannot alter the role ‘db_owner’, because it does not exist or you do not have permission.
Can a Login that has Control Server Permissin Create a Database or read data in a database?
With ControlServerLogin we can read any data or create a database as follows.
I wanted to briefly show a few features. I listed what you could do at the beginning of the article. It will be a bit hard to explain them all here one by one. You can try them all one by one.
Restrict DBA From Viewing Data
If we go back to our topic; we want to prevent someone that has Control Server permission from seeing the data. That’s why we need to deny SELECT ALL USER SECURABLES at Instance level to prevent anyone with Control Server permission from reading the data.
Right-click on Instance again and click on properties and select the controlserverLogin from the “Login and roles” section. And from the permission section below, select Select All User Securables and click on DENY.
Now, when you try to read data with controlServerLogin, you receive the error as follows.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object ‘DatabaseLog’, database ‘AdventureWorks2014Yeni’, schema ‘dbo’.
How To Impersonate a sysadmin with a login that has Control Server Permission
As a final step, I will end the article by explaining how controlserverLogin will impersonate a sysadmin, and how can we prevent it from impersonating sysadmin.
We just received an error when we tried to read the data. Let’s try to read the data again by impersonating sysadminLogin.
You can do this with the following script.
|
1 2 3 4 |
EXECUTE AS LOGIN = 'sysadminLogin' GO SELECT * FROM [AdventureWorks2014Yeni].[dbo].[DatabaseLog] |
As you can see, while normally we can not read data, we were able to read data with controlserverLogin by impersonating sysadminLogin.
In Stored Procedures, we can do the same as follows.
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE [dbo].[procedurename] WITH EXECUTE AS 'sysadminLogin' AS . . . |
How To Deny Impersonate Permission from a Login that has Control server Permission
As I mentioned at the beginning of the article, we should DENY IMPERSONATE ANY LOGIN privilege at instance level. You must DENY the “Select All User Securables” permission.
