http://www.sqlservercentral.com/blogs/brian_kelley/2009/03/06/control-server-vs-sysadmin-membership/

Printed 2014/12/19 08:15PM

CONTROL SERVER vs. sysadmin membership

2009/03/06

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;
GO

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

GRANT CONTROL SERVER TO TestControlServer;
GO

DENY CONNECT SQL TO TestControlServer;
GO

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';
GO

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.

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.