SQL Server provides various built-in server roles that allow you to assign specific permissions and privileges to users. One of these roles is the securityadmin role, which has the ability to assign server-level permissions to logins. However, there are some important considerations and potential security risks associated with the securityadmin role that you should be aware of.

The Securityadmin Role in SQL Server 2005/2008

In SQL Server 2005/2008, the securityadmin fixed server role has the ability to grant CONTROL SERVER permissions to logins. This means that a member of the securityadmin role can give a login the ability to perform any action on the SQL Server. While this may seem like a potential security risk, it is by design and confirmed by Microsoft.

It is important to note that the securityadmin role in SQL Server 2005/2008 is different from its behavior in SQL Server 2000 and previous versions. In SQL Server 2000, a member of the securityadmin role could only make another login a member of the securityadmin role and could not grant permissions equivalent to the sysadmin fixed server role.

Potential Attack Vectors

There are two possible attack vectors that can be exploited by a member of the securityadmin role to escalate privileges to the equivalent of the sysadmin role:

1. Issuing a CONTROL SERVER permission
2. Using the IMPERSONATE command

By granting a login CONTROL SERVER rights or granting IMPERSONATE on the sa login (or its equivalent), a member of the securityadmin role can gain elevated privileges. These attack vectors can be used to bypass the intended security restrictions and potentially compromise the SQL Server.

Recommendation: Grant Server Permissions, Don’t Use Roles

Given the potential security risks associated with the securityadmin role, Microsoft recommends avoiding the use of server roles and instead using server permissions. By revoking any securityadmin membership and granting ALTER ANY LOGIN permissions to logins, you can provide the necessary permissions without granting excessive privileges.

Granting ALTER ANY LOGIN permissions allows the login to create and drop logins, reset passwords on non-sysadmin logins, and grant database-level permissions. However, it does not permit the login to grant any server-level permissions, addressing the main issue with the securityadmin role.

A Server-level DDL Trigger as a Workaround

If you have already implemented the securityadmin role or need logins to be able to grant server-level permissions (excluding CONTROL SERVER and IMPERSONATE), you can use a server-level DDL trigger as a workaround. This trigger can prevent logins without CONTROL SERVER permissions from granting these specific permissions.

Here is an example of a server-level DDL trigger that limits the granting of CONTROL SERVER and IMPERSONATE permissions:

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;

This trigger checks for the granting of CONTROL SERVER or IMPERSONATE permissions and rolls back the grant if the login does not have CONTROL SERVER rights. It provides an additional layer of security by preventing unauthorized granting of these permissions.

It is important to note that this trigger does not block other server-level permissions, such as VIEW SERVER STATE, that can be granted by a login with appropriate rights.

Conclusion

The securityadmin role in SQL Server provides powerful capabilities for managing server-level permissions. However, it is crucial to understand the potential security risks associated with this role and take appropriate measures to mitigate them.

By following Microsoft’s recommendation to grant server permissions instead of using roles, and by implementing a server-level DDL trigger to limit the granting of CONTROL SERVER and IMPERSONATE permissions, you can enhance the security of your SQL Server environment.