Sql-server – What role is able to grant role permissions on MSSQL 2016

permissionssql serversql-server-2016

Unfortunately, we'll have to remove db_owner role from not admin users, because people were abusing it.

We'll split roles permissions in 3 groups:

1) readonly (SELECT) with specific write (INSERT,UPDATE,DELETE) on some tables

2) DDL (ALTER) without RW

3) "restricted admin" custom role, able to add other AD groups and users to existing roles

But I can't find what role is able to grant and revoke permissions without being a db_owner. I tried db_accessadmin and db_securityadmin, but when a user with these roles tries to add another user to db_datareader or db_datawriter it fails saying he doesn't have permission.

Best Answer

A restricted admin should only add users to roles through ALTER ROLE. The ability to grant new permissions to a role or user should be reserved for a real admin.