Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

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

For SQL Server 2005/2008, Consider SecurityAdmin = SysAdmin

Note: Republishing because the article covering the issue and a workaround has been published here on SSC.

In a recent thread here on SQL Server Central, it was pointed out that a member of the securityadmin fixed server role could do the following:

  1. Create a new login.
  2. GRANT CONTROL SERVER to the new login.
  3. Use the login for the equivalent of sysadmin role membership.

Now, if you're used to the SQL Server 2000 securityadmin role, this doesn't register, as the methods to elevate privilege by a securityadmin were blocked in that version. For instance, a securityadmin couldn't do any of the following:

  • Make a login a member of the sysadmin role.
  • Change the password of a login that was a member of the sysadmin role.
  • Grant equivalent permissions to sysadmin role membership.

However, with SQL Server 2005 came granular permissions in the form of securables. Among those permissions was CONTROL SERVER, which as might be guessed, is a server level permission. Well, members of the securityadmin role have the ability to ALTER ANY LOGIN (with an exception... members of the sysadmin role) and to GRANT/REVOKE any server level permission. That's what Books Online indicates. So while BOL does state that this attack vector is possible, it doesn't state it very clearly. It takes a bit of extrapolation to realize that with CONTROL SERVER, a member of the securityadmin fixed server can effectively create a login that has sysadmin level rights. BOL will be updated accordingly, based on what Microsoft indicated this afternoon.

So what does this mean with respect to using the securityadmin server role? It means that if someone who is a member of the securityadmin role should be considered a member of the sysadmin role. If you have audit requirements that restrict sysadmin rights, then it is not a sufficient control to allow them to only be a member of securityadmin. Treat securityadmin like sysadmin. It basically boils down to that. So what if you need securityadmin level rights, like from the SQL Server 2000 days? Here's what to do:

  1. Grant ALTER ANY LOGIN rights to the login that needs the securityadmin like rights.
  2. Map the login to a user account in all the databases it needs to control access to (using CREATE USER).
  3. Grant ALTER ANY USER in those databases to those user accounts.
  4. Grant the ability to assign any other permissions as necessary to those user accounts.

If you've already got securityadmin implemented and this retrofit is going to prove onerous, I'm working on a couple of remediation tricks not only for blocking CONTROL SERVER, but also another method of privilege escalation. I'm hoping to get that to Steve by this weekend as an article submission. If it's accepted, I'll update this post and put a new blog post up pointing to the article.

 

Comments

Posted by ALZDBA on 23 July 2010

For MS it's the cheapest way to just document it in BOL. Case closed.

For us, ( audited consumers ) it means we must implement the workaround ASAP to get back in line with approved business regulations. (SOx, ...)

IMMHO this should be considered a BUG.

Thank you for the folluw up.

Posted by SalvageDog on 23 July 2010

If not a bug, then certainly a design flaw.

Posted by Jason Brimhall on 25 July 2010

Certainly a bug.  Thanks for the heads up.

Posted by Tbull95 on 27 July 2010

Seems like a little pre-planning would prevent future headaches. This is just plain BUGLY. Thanks for the tip.

Posted by krishnaraj9 on 18 August 2010

I wont agree with this comment "a member of the securityadmin fixed server can effectively create a login that has sysadmin level rights", rather I would say it can create a user with securityadmin privileges.

Posted by K. Brian Kelley on 18 August 2010

Sorry, krishnaraj9, I'm going to have to disagree with you.

A securityadmin can create SQL Server logins and can grant access to Windows logins.

A securityadmin can grant CONTROL SERVER to any login except itself. CONTROL SERVER is the equivalent permission that the sysadmin fixed server role.

Therefore, a member of the securityadmin role can effectively create a login that has sysadmin level rights. This has been confirmed by Microsoft.

Posted by ThomasLL on 2 September 2010

Good detective work, Brian.

Thanks and God Bless,

Thomas

Posted by Forsqlserver on 27 April 2011

It is a boon and bane both.

Leave a Comment

Please register or log in to leave a comment.