Friday, May 6, 2011

Sysadmin & SecurityAdmin Fixed Server Roles

Did you know that in v2005/v2008, a member of the securityadmin fixed server role can grant CONTROL SERVER permissions?  That is far too close to sysadmin for my comfort.  Not only that, but they can add themselves to the SA role at anytime, to do anything.  I always check security on servers that I have inherited, so that I know who has securityadmin or sysadmin on each instance. This is just a quick check on those two fixed server roles:

  SELECT 
     l.name, 
     l.denylogin, 
     l.isntname, 
     l.isntgroup, 
     l.isntuser
  FROM 
     master.sys.syslogins l
  WHERE 
     l.sysadmin = 1 OR l.securityadmin = 1
  ORDER BY 
     l.isntgroup, 
     l.isntname, 
     l.isntuser


At the absolute minimum, make sure that you know about everybody returned in that list.  You might even try logging in as each one of them, with a blank password.  Please also take a look at this article by Brian Kelley, on CONTROL SERVER: Restricting SecurityAdmin on SQL Server 2005/2008.

No comments:

Post a Comment