SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

CONTROL SERVER vs. sysadmin membership

In a previous blog post on Detecting When a Login Has Implicit Access to a Database, I mentioned that having CONTROL SERVER rights means having implicit rights into the databases. Robert Davis posted a comment asking if there was a difference with respect to explicit permissions between being a member of the sysadmin fixed server role and having CONTROL SERVER rights. Members of the sysadmin fixed server role bypass security meaning even if you issue an explicit DENY, they still have access. The same as someone who maps in as dbo for a given database. You could DENY to public, which should encompass said user, but it doesn't matter. The DENY is ignored.

 However, members of the db_owner fixed database role do NOT ignore DENYs. So that got me to testing to see if there was a difference between the sysadmin fixed server role and CONTROL SERVER rights and there is. Having CONTROL SERVER righs still means you can be blocked. An easy example is the following, we'll create a login, grant it CONTROL SERVER rights, the DENY CONNECT SQL for that login.

USE master;

CREATE LOGIN TestControlServer WITH PASSWORD 'AStr0ng0ne,Sort0f...';


DENY CONNECT SQL TO TestControlServer;

If you then attempt to connect, you'll get that the login failed. The explicit DENY is preventing the login, even though the login has CONTROL SERVER rights. But what happens if we make the login a member of the sysadmin fixed server role?

EXEC sp_addsrvrolemember 'TestControlServer''sysadmin';

If you try it again, this time you should be able to connect. Therefore, having CONTROL SERVER rights may be the functional equivalent of having membership in the sysadmin fixed server, except that explicit DENYs do still affect the login.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by ALZDBA on 16 March 2009

A big downside of this "Control Server" is that it doesn't show in SSMS (2005 nor 2008) (in the security tree) !!

So the only way to find it is using these queries from previous blog post on Detecting When a Login Has Implicit Access to a Database!

Posted by igalgre on 16 March 2009

How would the control server role behave in cases when one needs to recover a database or a server.  I would think this would be a non starter for server recovery for instance.

Is it wise to have a second tier DBAs?

Leave a Comment

Please register or log in to leave a comment.