CONTROL SERVER vs. sysadmin/sa:
permissions, system procedures, DBCC, automatic schema creation and privilege escalation caveats
Since SQL Server 2005, the server wide permission CONTROL SERVER has been existing. In principle being an alternative to sysadmin-membership, it did not turn out to be much more than a shelf warmer. – Little known and even less used.
One of the main reasons for this was the absence of an option to grant this permission to a group of principals/Logins on server-level.
Since SQL Server 2012, it has been possible to define custom server-roles, so now this permission is used more and more.
However, it is no complete replacement for sysadmin. In the following, I want to demonstrate to my readers exactly why this is, where the differences are and where it can even be a risk.
I will take as a given basic knowledge about the behavior of sysadmin and dbo and therefore focus on the things that are different when using CONTROL SERVER.
First of all, a new Login, „DBA_TheDude“, is created – the password of course adhering strongly to my rules (for blogs). 😉
This is followed by a new server role “Role_DBA” to which the Login will be added as a member.
And since this role is meant for administrators, it receives the most extensive permission: CONTROL SERVER.
Next we log onto the system as DBA and have “our (effective) rights” displayed. 🙂 
| entity_name | permission_name |
| server | CONNECT SQL |
| server | SHUTDOWN |
| server | CREATE ENDPOINT |
| server | CREATE ANY DATABASE |
| server | CREATE AVAILABILITY GROUP |
| server | ALTER ANY LOGIN |
| server | ALTER ANY CREDENTIAL |
| server | ALTER ANY ENDPOINT |
| server | ALTER ANY LINKED SERVER |
| server | ALTER ANY CONNECTION |
| server | ALTER ANY DATABASE |
| server | ALTER RESOURCES |
| server | ALTER SETTINGS |
| server | ALTER TRACE |
| server | ALTER ANY AVAILABILITY GROUP |
| server | ADMINISTER BULK OPERATIONS |
| server | AUTHENTICATE SERVER |
| server | EXTERNAL ACCESS ASSEMBLY |
| server | VIEW ANY DATABASE |
| server | VIEW ANY DEFINITION |
| server | VIEW SERVER STATE |
| server | CREATE DDL EVENT NOTIFICATION |
| server | CREATE TRACE EVENT NOTIFICATION |
| server | ALTER ANY EVENT NOTIFICATION |
| server | ALTER SERVER STATE |
| server | UNSAFE ASSEMBLY |
| server | ALTER ANY SERVER AUDIT |
| server | CREATE SERVER ROLE |
| server | ALTER ANY SERVER ROLE |
| server | ALTER ANY EVENT SESSION |
| server | CONTROL SERVER |
Sounds good. Nothing seems to be missing at first glance. Or does it?
Let’s compare it to the list of all permissions which exist on server-scope:
Ok, we really do have all permissions which can be granted (at server scope). This is also documented: Permissions (Database Engine) and here: GRANT Server Permissions (Transact-SQL)
Those permissions and commands really do work as documented.
But how about system stored procedures?
Why is that now?
Quite simple. Most do probably remember from SQL 2000 times, that security wise critical system procedures include a check for membership in certain roles. And most of them still do the same today. One can call it an artefact of the then inflexible permission-management.
When we look into the code of sp_readerrorlog, this is what is being done there as well:
Okay. Of course we can work around this code by calling xp_readerrorlog directly.
This does not help in the GUI-presentation in Management Studio but we can use it as a manual workaround.
Among other system procedures that are called via SSMS and refuse execution are: sp_addumpdevice – for backup-devices sp_enum_oledb_providers – is used for viewing and configuring Linked Servers.
sp_addlinkedserver on the other hand can be executed without problems – just not via the GUI because of the just mentioned procedure.
Setting up Database-Mail via the GUI is also reserved for sysadmins. It should work via script though (untested).
Also the configuration of the distributor for replication by means of sp_adddistributor is not possible without the sysadmin bit. Other replication tasks are partially possible even via the GUI.
Under SQL Server 2005 – 2008R2, to add/remove (server) role members, solely the system procedure sp_addsrvrolemember / sp_dropsrvrolemember exists. This one also conducts a server-role-membership-check. – Under SQL Server 2012 this procedure still exists for backwards-compatibility reasons, but this specific check has been removed from the code. Thereby it now behaves like the direct call of ALTER SERVER ROLE {RoleName} ADD MEMBER {Loginname}.
Same applies to sp_addrolemember / sp_droprolemember for database roles.
So much for system stored procedures. Quite simple after all, if you know the background.
Therefore, wherever possible: use DDL-commands, as it is advised since SQL Server 2005.
Side-note: there are at least 7 variations for the check of sysadmin-membership. 🙂 🙁
A teaser:
The most frequent error messages which are triggered if applicable are:
| message_id | text |
| 14126 | You do not have the required permissions to complete the operation. |
| 14260 | You do not have sufficient permission to run this command. Contact your system administrator. |
| 15003 | Only members of the %s role can execute this stored procedure. |
| 15247 | User does not have permission to perform this action. |
| 21089 | Only members of the sysadmin fixed server role can perform this operation. |
| 22904 | Caller is not authorized to initiate the requested action. DBO privileges are required. |
A complete list of procedures that check for sysadmin-membership is included at the end of this post.
Let’s come to another important area:
DBCC commands
That’s a good start, isn’t it..
The „rule“ for DBCC is even simpler: Almost every DBCC-command checks for sysadmin.
From DBCC CHECKDB via DBCC LOGINFO to DBCC TRACEON. Thus some quite valuable commands also for external support. – In fact even DBCC HELP is only allowed for sysadmins…
The only exceptions known to me are: DBCC SHOW_STATISTICS
- one needs to be at least ddl_admin or owner of the table
DBCC DETACHDB
- this is a special case, since it’s executed via sp_detach_db
- db_owner or dbo of the database is sufficient
DBCC FREEPROCCACHE and DBCC SQLPERF
- Those require only the ALTER SERVER STATE permission.
DBCC DROPCLEANBUFFERS on the other hand requires sysadmin-role membership.
Those permissions are documented quite well, by the way.
Database permissions
With CONTROL SERVER, a principal has full access to all databases. Almost, but only almost like sysadmins, which are, as is well-known, mapped to “dbo”.
Logins with merely CONTROL SERVER permission are not(!) mapped to dbo.
The consequences of this can be seen in the following:
–>
SSMS:
What happened?
Do you remember the pre-SQL 2012 problem in which windows groups could not be assigned a default schema? And what happened if a developer forgot to specify a schema when creating an object?
Right. This is exactly the same issue: a schema with the Login-Name of the developer has been created. A true joy for later clean-up, to identify and drop the „right wrong” schemas. This Issue (one of the most voted Connect-Items) has been resolved with SQL Server 2012.
Unfortunately, the handling of the CONTROL SERVER permission has been overlooked to be fixed. I did create a connect item: „Login with CONTROL SERVER Permission Creating an Object without specifying Schema leads to creation of new Schema with Login-Name”. – Unfortunately too late. :-/
Probably an identical code-block is being used for that, since just like in the Windows-Group scenario, this implicit creation of the schema is uncatchable.
Conclusion: Always specify a schema when creating an object as a CONTROL SERVER granted principal – best: always specify a schema. (there are other reasons for that, too)
Privilege-Escalation-risk:
I want to finish off with a security-wise extremely important hint:
Logins with CONTROL SERVER permission can impersonate EVERY Login. ALL OF THEM. Thus also all sysadmins and sa itself! – And even if SQL authentication is not active!
And this is how this can be marvellously exploited:
Those who wants to protect themselves from this form of privilege escalation must DENY impersonating. – And why would you not want to do that since this eventually would put the whole concept of CONTROL SERVER in question (?!) – Because this is the final and crucial difference:
Sysadmin-members cannot be restricted anything via DENY. Yet principals with mere CONTROL SERVER permissions can.
This is how all plain DDL/DML commands work.
So, to say it clearly again, because this is vital:
A Login with CONTROL SERVER Permission only makes sense when at the same time he is explicitly denied impersonation of any sysadmin (or better: any Logins). Otherwise you may as well just make him sysadmin right away!
Unfortunately this only works via “DENY IMPERSONATE ON LOGIN::[Loginname]”. This is because IMPERSONATE is hierarchically a direct subordinate of CONTROL SERVER!
For a changing list of system administrators, one has a real problem, in case of not remembering this crucial Deny! (Next risk!)
– At the latest at this point one should address the Auditing feature of SQL Server.
My conclusion and lessons learned therefore are:
CONTROL SERVER itself is a consistent continuation of the efforts of the security team to make SQL Server even simpler and thereby more robust.
Because of the impersonate-permissions, a privilege escalation is really simple. In order to prevent this, one should be familiar with the permission hierarchy of SQL Server. – As always: “Knowledge leads to security.”
What really does hurt, is one of the best application cases: Permissions for support-personnel, internal or external. The missing possibility to grant permissions to DBCC directly throws a monkey wrench in our plans. If one does not want to create a procedure as wrapper for every important DBCC command, there is no way around sysadmin in those scenarios. (!)
In the end, a strict “separation of duties” is the iron goal.
Here you can read more about it – you should also check out the “SQL Server Separation of Duties Framework” on codeplex with many good ideas:
https://techcommunity.microsoft.com/t5/SQL-Server/Separation-of-Duties-for-DBA-s/ba-p/383915
Update 04-2014:
With SQL Server 2014 CONTROL Server becomes a bit safer. You can read more on the new possibilities here:
A displacement of „sa“ is not planned by the way, but with every release the scenarios where sa/sysadmin is imperatively needed become less.
Happy securing,
Andreas
Enclosed the list of all system procedures under SQL Server 2012 that check for sysadmin-role membership (171 – vs. 171 under SQL server 2008. SQL Server 2014 CTP 1 still the same):
(Hint: Altogether 197 system procedures check for server role membership like diskadmin or serveradmin and so on)
| Module_Name |
| fn_yukonsecuritymodelrequired |
| sp_add_agent_parameter |
| sp_add_agent_profile |
| sp_adddatatype |
| sp_adddistributiondb |
| sp_adddistributor |
| sp_addqreader_agent |
| sp_addsubscriber |
| sp_addsubscriber_schedule |
| sp_addtabletocontents |
| sp_attachsubscription |
| sp_cdc_cleanup_change_table |
| sp_cdc_disable_db |
| sp_cdc_disable_table |
| sp_cdc_drop_job |
| sp_cdc_enable_db |
| sp_cdc_enable_table |
| sp_cdc_restoredb |
| sp_cdc_vupgrade |
| sp_certify_removable |
| sp_change_agent_parameter |
| sp_change_agent_profile |
| sp_change_subscription_properties |
| sp_change_users_login |
| sp_changedistpublisher |
| sp_changedistributiondb |
| sp_changedistributor_password |
| sp_changedistributor_property |
| sp_changemergesubscription |
| sp_changeqreader_agent |
| sp_changereplicationserverpasswords |
| sp_changesubscriptiondtsinfo |
| sp_checkinvalidivarticle |
| sp_copysubscription |
| sp_create_removable |
| sp_cycle_errorlog |
| sp_dbcmptlevel |
| sp_dbmmonitoraddmonitoring |
| sp_dbmmonitorchangealert |
| sp_dbmmonitordropalert |
| sp_dbmmonitordropmonitoring |
| sp_dbmmonitorhelpalert |
| sp_dbmmonitorhelpmonitoring |
| sp_dbmmonitorresults |
| sp_dbmmonitorupdate |
| sp_dbremove |
| sp_drop_agent_parameter |
| sp_drop_agent_profile |
| sp_dropdatatypemapping |
| sp_dropdistpublisher |
| sp_dropdistributiondb |
| sp_dropdistributor |
| sp_dropmergepullsubscription |
| sp_droppullsubscription |
| sp_dropsubscriber |
| sp_dsninfo |
| sp_enumdsn |
| sp_flush_commit_table_on_demand |
| sp_generate_agent_parameter |
| sp_get_distributor |
| sp_get_Oracle_publisher_metadata |
| sp_getagentparameterlist |
| sp_getdefaultdatatypemapping |
| sp_grant_publication_access |
| sp_help_agent_default |
| sp_help_agent_parameter |
| sp_help_agent_profile |
| sp_helpdistpublisher |
| sp_helpdistributor |
| sp_helpmergesubscription |
| sp_helpqreader_agent |
| sp_helpreplicationdboption |
| sp_identitycolumnforreplication |
| sp_IHValidateRowFilter |
| sp_IHXactSetJob |
| sp_link_publication |
| sp_monitor |
| sp_MSadd_distribution_agent |
| sp_MSadd_logreader_agent |
| sp_MSadd_merge_agent |
| sp_MSadd_snapshot_agent |
| sp_MSadd_subscriber_schedule |
| sp_MSadd_tracer_history |
| sp_MSadd_tracer_token |
| sp_MScdc_cleanup_job |
| sp_MScdc_db_ddl_event |
| sp_MScdc_ddl_event |
| sp_MSchange_distribution_agent_properties |
| sp_MSchange_logreader_agent_properties |
| sp_MSchange_merge_agent_properties |
| sp_MSchange_snapshot_agent_properties |
| sp_MSchangedynamicsnapshotjobatdistributor |
| sp_MSchangedynsnaplocationatdistributor |
| sp_MScheck_pull_access |
| sp_MScleanupmergepublisher_internal |
| sp_MSclear_dynamic_snapshot_location |
| sp_MScreate_dist_tables |
| sp_MSdbuserpriv |
| sp_MSdeletefoldercontents |
| sp_MSdrop_6x_replication_agent |
| sp_MSdrop_merge_agent |
| sp_MSdrop_snapshot_dirs |
| sp_MSdropmergedynamicsnapshotjob |
| sp_MSdynamicsnapshotjobexistsatdistributor |
| sp_MSenumallpublications |
| sp_MSfetchAdjustidentityrange |
| sp_MSfix_6x_tasks |
| sp_MSforce_drop_distribution_jobs |
| sp_MSget_agent_names |
| sp_MSget_jobstate |
| sp_MSget_oledbinfo |
| sp_MSget_publication_from_taskname |
| sp_MSgetdbversion |
| sp_MSgetmaxsnapshottimestamp |
| sp_MShelp_repl_agent |
| sp_MShelp_replication_status |
| sp_MShelp_snapshot_agent |
| sp_MShelpconflictpublications |
| sp_MShelpdynamicsnapshotjobatdistributor |
| sp_MShelplogreader_agent |
| sp_MShelpsnapshot_agent |
| sp_MShelptranconflictcounts |
| sp_MSinit_publication_access |
| sp_MSreinit_failed_subscriptions |
| sp_MSremoveoffloadparameter |
| sp_MSrepl_backup_complete |
| sp_MSrepl_backup_start |
| sp_MSrepl_createdatatypemappings |
| sp_MSrepl_dropdatatypemappings |
| sp_MSrepl_enumarticlecolumninfo |
| sp_MSrepl_enumpublications |
| sp_MSrepl_enumpublishertables |
| sp_MSrepl_enumsubscriptions |
| sp_MSrepl_enumtablecolumninfo |
| sp_MSrepl_getdistributorinfo |
| sp_MSrepl_startup_internal |
| sp_MSreplagentjobexists |
| sp_MSreplcheck_permission |
| sp_MSreplcheck_pull |
| sp_MSreplcheck_subscribe |
| sp_MSreplcheck_subscribe_withddladmin |
| sp_MSreplcopyscriptfile |
| sp_MSreplremoveuncdir |
| sp_MSsetalertinfo |
| sp_MSSetServerProperties |
| sp_MSsetupnosyncsubwithlsnatdist |
| sp_MSsetupnosyncsubwithlsnatdist_cleanup |
| sp_MSsetupnosyncsubwithlsnatdist_helper |
| sp_MSstartdistribution_agent |
| sp_MSstartmerge_agent |
| sp_MSstartsnapshot_agent |
| sp_MSstopdistribution_agent |
| sp_MSstopmerge_agent |
| sp_MSstopsnapshot_agent |
| sp_MSupdate_agenttype_default |
| sp_oledbinfo |
| sp_procoption |
| sp_removedbreplication |
| sp_removesrvreplication |
| sp_replication_agent_checkup |
| sp_replicationdboption |
| sp_resetstatus |
| sp_restoredbreplication |
| sp_SetAutoSAPasswordAndDisable |
| sp_setdefaultdatatypemapping |
| sp_updatestats |
| sp_validatelogins |
| sp_vupgrade_mergeobjects |
| sp_vupgrade_replication |
| sp_vupgrade_replsecurity_metadata |
| xp_repl_convert_encrypt_sysadmin_wrapper |
Hi Andreas, in my search for a secure setup of SQL Agent jobs, I came across your valuable post.
Since some jobs need to run dbcc checkdb, I checked MS docs, which specify that dbo privileges for the given database would suffice, and I can confirm that that works (in SQL 2017). You mention that almost all dbcc commands require sysadmin (except a few). Could it be that that has changed since you wrote this post?
Kind regards, Willem
Hi Willem.
I am so sorry abou this super late reply. I did very bad with keeping an eye on comments among all the spam in general.
To answer your question. Yes, Microsoft and since 2018 I personally have been pushing for lowering some of the requirements and place them under VIEW SERVER STATE also.
I don’t have a complete list of changes at hand, but this article is probably about 20-25 % outdated. The online documentation should be accurate though – at least I have tried to maintain it a lot lately. If you encounter any mismatches in the docs please let me know.
Thank you and sorry agan for the late reply
Andreas
Good information but CONTROL SERVER is still very dangerous to be used. It can easily be escalated to an sa account or sysadmin, even if the IMPERSONATION on the account is disabled/denied.
just like that the securityadmin server role should not be used either because of the CONTROL SERVER as anybody with securityadmin server role can easily escalate itself or any other account to sa or sysadmin.
If needed I can provide a demonstration.
Thank you.
Hello Zeshan,
yes, you are right. There are several ways to escape the CONTROL SERVER “boundaries” and elevate to sysadmin still left. The phrase I used was “more secure than before”. Maybe that is not sufficient. On the other hand I did not intend to show all PE techniques in public.
If you think you know a way that is unknown to others/me, feel free to share with me privately and I will have a look.
Improving security in SQL Server is now my current Job at Microsoft.
best regards
Andreas