With the new securables since SQL Server 2005, Microsoft has advised that we start moving away from the fixed server roles like sysadmin and securityadmin. In fact, in light of the fact that securityadmin can privilege escalate to sysadmin, it was stated that the solution was to look at permissions against the securables. Naturally, since the CONTROL SERVER permission is to be the replacement for sysadmin server role membership, it's something I've told folks to look for when they audit SQL Server. And well they should, because from an attack/compromise/steal perspective, CONTROL SERVER gives one all he or she needs to do just that. But from a day-to-day administration perspective, it's just not satisfactory. Why do I say this?

Well, I was trying to modify Ola Hallengren's wonderful scripts for backup and maintenance routines for SQL Server 2005 and SQL Server 2008 to also work for logins with CONTROL SERVER permissions. He's got a bit of code right at the start that verifies you're a sysadmin member:

IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
  RAISERROR('The server role SysAdmin is needed for the installation.',16,1)
  SET @Error = @@ERROR
END  

Which I naturally modified to validate a login with CONTROL SERVER like so:

IF IS_SRVROLEMEMBER('sysadmin') = 0 AND HAS_PERMS_BY_NAME(NULL, NULL, 'CONTROL SERVER') = 0
BEGIN
  RAISERROR('The server role SysAdmin or the CONTROL SERVER permission is needed for the installation.',16,1)
  SET @Error = @@ERROR
END  

Thinking that was all I needed to do, I ran the script and then promptly ran into an error with sp_readerrorlog. See, sp_readerrlog has this little tidbit:

IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)  
BEGIN  
    RAISERROR(15003,-1,-1, N'securityadmin')  
    RETURN (1)  
END    

Okay, I can fix this. If you look at the T-SQL, sp_readerrlog is just a wrapper around xp_readerrorlog. So replace dbo.sp_readerrorlog with sys.xp_readerrorlog and we're good, right? Not exactly. I ran the script again and then a bunch of errors fired due to sp_verify_jobstep. Here's what's causing the failure from sp_verify_jobstep:

  --Only sysadmin can specify @output_file_name  
  IF (@output_file_name IS NOT NULL) AND  (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)  
  BEGIN  
    RAISERROR(14582, -1, -1)  
    RETURN(1) -- Failure      
  END  

A member of the sysadmin fixed server role automatically returns 1 when IS_SRVROLEMEMBER() is checked for securityadmin role membership. Someone who just has CONTROL SERVER rights is not a member of either and as a result, this function will return a 0 when the function is checked. And while there's a workaround for sp_readerrorlog, there's not one for sp_verify_jobstep, at least one that isn't intrusive.  These aren't the only cases where I've run into issues. But these popped out today just from Ola's scripts and reveal that while the advice is to use the server-level securable permissions, it's just not practical yet.