http://www.sqlservercentral.com/blogs/brian_kelley/2010/09/01/for-sql-server-2005-2008-consider-securityadmin-sysadmin/

Printed 2014/07/24 10:13AM

For SQL Server 2005/2008, Consider SecurityAdmin = SysAdmin

2010/09/01

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:

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.

 


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