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
|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:
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 ANY AVAILABILITY GROUP
ADMINISTER BULK OPERATIONS
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
ALTER ANY SERVER AUDIT
CREATE SERVER ROLE
ALTER ANY SERVER ROLE
ALTER ANY EVENT SESSION
CONNECT ANY DATABASE
IMPERSONATE ANY LOGIN
Query SQL Server Instance Level Permissions
With the following query, you can see the permissions of the login you are connected to has:
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.
CREATE LOGIN [controlservertest] WITH PASSWORD=N'xx', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [controlservertest] FOR LOGIN [controlservertest]
GRANT ALTER TO [controlservertest]
If we want to grant db_owner privileges to login using the following script, we receive the error as follows.
ALTER ROLE [db_owner] ADD MEMBER [controlservertest]
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.
EXECUTE AS LOGIN = 'sysadminLogin'
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.
CREATE PROCEDURE [dbo].[procedurename]
WITH EXECUTE AS 'sysadminLogin'
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.