Recently, a forum question asked about the fact that in SQL Server 2005/2008, the securityadmin fixed server can grant CONTROL SERVER permissions. This is the equivalent of the rights of the sysadmin fixed server role with some minor exceptions. As the name would imply, the securityadmin can therefore give a login the ability to do anything on a given SQL Server. This would seem to be mitigated by the fact that a member of the securityadmin role cannot grant permissions to itself unless it has higher rights (member of sysadmin or has CONTROL SERVER permissions itself).

SecurityAdmin = SysAdmin

Upon doing the research and checking in with Microsoft, this privilege was confirmed and is by design. The securityadmin fixed server role has the ability to assign any server level permission. This would include CONTROL SERVER. Now this departs from how we're used to the securityadmin role working from versions of SQL Server 2000 and previous. At most a member of the securityadmin role could make another login a member of the securityadmin role. It could not grant permissions that were on par to being a member of the sysadmin fixed server role. And, in fact, some of this behavior is also seen in SQL Server 2005/2008. For instance:

• Members of the securityadmin role cannot make any login a member of the sysadmin fixed server role.
• Members of the securityadmin role cannot reset the passwords on any logins which are members of the sysadmin fixed server role.

This is behavior that we're used to from SQL Server 2000 so it seems like a disconnect that the securityadmin can issue a CONTROL SERVER permission to a login. But since it can, it must be treated as an equivalent of the sysadmin server role. So if you did a straight migration from SQL Server 2000 to 2005/2008 expecting the roles to function the same, you have an issue. This is an audit point for some folks to be able to manage security on a SQL Server but not have complete control of the server and now the "control" (that which serves as a safeguard) is broken. And that's because there are two possible attack vectors where a member of securityadmin may escalate privileges to be the equivalent of the sysadmin role. Both take advantage of the fact that a securityadmin can also create logins for a given SQL Server and grant said logins permissions above that which is held by a securityadmin.

The Two Attack Vectors

The first is simply to issue a CONTROL SERVER permission. The attack vector looks like this:

1. Create or identify a login to be used.
2. Grant that login CONTROL SERVER rights.
3. Use that login to connect to the SQL Server.

The second attack vector is similar and if you're only watching for CONTROL SERVER grants, you'll miss it. It involves the use of IMPERSONATE.

1. Create or identify a login to be used.
2. Grant that login IMPERSONATE on the sa login (or whatever it was renamed to).
3. Use that login to connect to the SQL Server.
4. Wrap the appropriate commands with EXECUTE AS LOGIN = 'sa';

The Recommendation: Grant Server Permissions, Don't Use Roles

Because it is documented that the securityadmin role can do these sorts of things, the ability to use these two attack vectors is not considered a bug. Therefore, at the present time there is no plan for returning the behavior to function like it did in SQL Server 2000. Rather, the Books Online documentation will be updated to give a clearer understanding of what securityadmin can do. Microsoft's recommendation is to avoid the use of the server roles and use server permissions instead. So if you want to give a login the equivalent permissions as securityadmin (at least, securityadmin in the SQL Server 2000 days), you'll want to do the following:

1. Revoke any securityadmin membership given to logins.
2. GRANT ALTER ANY LOGIN to those logins instead.

This will give the login the ability to:

• Create and drop logins
• Reset passwords on any logins that aren't members of the sysadmin fixed server role and which do not have CONTROL SERVER rights
• Grant the logins the ability to connect to a given database on the SQL Server

It will not permit the logins to grant any server level permissions. That's the crux of the issue with the use of the securityadmin fixed server role. Now, if you need such permissions, this recommendation doesn't work for you. And obviously, securityadmin is now too powerful. But there is a workaround.

The Workaround: A Server-level DDL Trigger

If you've already implemented using securityadmin or you need the logins to be able to grant server-level permissions just not those server-level permissions, the recommendation doesn't work. Either it's a tedious chore to go back and undo the securityadmin membership and grant ALTER ANY LOGIN everywhere (and update the appropriate security documentation) or it's figuring out what permissions to allow the login to grant, etc. Since we're concerned with the granting of CONTROL SERVER and IMPERSONATE, a simple DDL trigger at the server level can prevent someone who doesn't have CONTROL SERVER permissions (members of the sysadmin role do) from doing so.

CREATE TRIGGER LimitSecurityAdmin
ON ALL SERVER
FOR DDL_SERVER_SECURITY_EVENTS
AS
BEGIN
 DECLARE @Login sysname;
 DECLARE @Permission NVARCHAR(100);
 DECLARE @ControlServerPerm TINYINT;

 SET @Permission = EVENTDATA().value('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)');
 SET @ControlServerPerm = (SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'CONTROL SERVER'));

 IF (@ControlServerPerm = 0) AND ((@Permission = 'control server') OR (@Permission = 'impersonate'))
 BEGIN
 ROLLBACK;
 PRINT 'You do not have the authority to grant/revoke ' + UPPER(@Permission) + ' on this server.';
 END;
END;
GO

With CONTROL SERVER, there's no gray area. Either you have the ability to control the server or you don't. IMPERSONATE is a different matter because IMPERSONATE could be granted on a login that isn't a member of the syadmin server role and which doesn't have CONTROL SERVER rights. However, to figure that out would involve a bit of text parsing, which is beyond what this simple trigger does. It merely looks for the granting of CONTROL SERVER or IMPERSONATE and it doesn't check to see who is being impersonated.

Should a login attempt to issue a grant against CONTROL SERVER or IMPERSONATE and that login not have CONTROL SERVER rights, this trigger will rollback the GRANT and it will return a message that indicates the login doesn't have the authority to issue the GRANT. A login with appropriate rights could grant any other server-level permission, for instance, VIEW SERVER STATE, and this trigger won't block it. Therefore, it sufficiently allows securityadmin the ability to manage most all of the server-level permissions while preventing access to the two we're most concerned about.