Press "Enter" to skip to content

Delegating Permissions Management in SQL Server

Andreas Wolter has people for that:

Both roles can GRANT/DENY/REVOKE any permission within a database. Since that makes members of these roles super powerful, their membership should always be tightly controlled and audited. (also see: Security concept: Audit Trail).
Consequently, most customers just keep this power to the main Administrators-group.

However, there are cases where it is useful to enable a set of non-admin users—let’s call them LowPermission-Managers—to work in a more self-sufficient way and enable them to grant some permissions to other users without the need to involve “the Administrators”.
The emphasis is on “some permissions” asthis only makes sense for low privileges. Making the LowPermissionManagers members of db_owner or db_securityadmin -roles would defeat the purpose.

Read on for two techniques which allow you to do just that.