0

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.

Improve this question
2

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.

Improve this answer
6
  • I understand. If needed we could create a custom role, if we can find out which permission is needed to add to that role. – Hikari Feb 10 '20 at 14:51
  • The point is that the roles and the assignment of permissions to those roles are part of the database schema. You should not need to change them frequently, or have any need for anyone other than a the database owner to do so. – David Browne - Microsoft Feb 10 '20 at 14:53
  • I agree. But the department that provides the infrastructure and with sa password doesn't cooperate. While some people on our department are very amateur and don't have proper responsibility to have DDL permission. – Hikari Feb 10 '20 at 14:55
  • It sounds like you may be looking for a technical solution to a non-technical problem. – David Browne - Microsoft Feb 10 '20 at 14:57
  • In example, when they need some data, instead of using the table that has that data, they create a new table, in the wrong DB, they create duplicated data then leave this table outdated. And then other people, not noticing that, use this wrong table and outdated data. Unfortunately we have to live with these people and not remove full access from them, neither make them follow any good practice. The only possible action os to restrict their permission to create tables to only a few DB and during restrict times. – Hikari Feb 10 '20 at 14:58
2

Since you mentioned default roles of SQL Server, I suggest you check the following graphs from Microsoft docs:

Server Level Roles and Permissions Source: Server-Level Roles

Database Level Roles and Permissions Source: Database-Level Roles

If you do not see a default role with the specific privileges you need, create your own role and grant it the right privileges.

Improve this answer
2
  • I think maybe db_securityadmin and db_accessadmin might do it? I'll try it and get back with the result! – Hikari Feb 11 '20 at 12:35
  • @Hikari, were you able to find the appropriate role? – Ronaldo Apr 18 '20 at 1:51
1

Ref: ALTER ROLE (Transact-SQL)

The behavior you are seeing is by design. @David Browne answered your question. I am going to elaborate on his answer.

But I can't find what role is able to grant and revoke permissions without being a db_owner.

If it is a custom role a user needs the following permissions.

  • ALTER permission on the role
  • ALTER ANY ROLE permission on the database
  • Membership in the db_securityadmin fixed database role

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.

Two roles mentioned are fixed database roles.

  • To change the membership in a fixed database role you need: Membership in the db_owner fixed database role

You cannot change the name of a built-in role name to make it act like custom role.

Improve this answer
3
  • I can create a custom role if needed. The permission I need to add to this role is the ability to change the membership (add and remove users from) of db_datareader, db_datawriter and db_ddladmin roles. Is it possible to create a custom role able to do that, not being db_owner? – Hikari Feb 10 '20 at 15:04
  • Please test. I think it is possible. – SqlWorldWide Feb 10 '20 at 16:18
  • That's the permission I'm looking for. docs.microsoft.com/en-us/sql/t-sql/statements/… lists permissions, but I can't find which one allows a user not in db_owner to add and remove membership to other users. – Hikari Feb 11 '20 at 12:08
1

The GRANT command in T-SQL also has the optional WITH GRANT OPTION

If you give your restricted users a specific permission and want to allow them to allow other users to have that permission then use the WITH GRANT OPTION part when you assign that permission.

Personally I am not a fan of the fixed database roles as I find them too encompasing. I prefer to define more granular permissions or roles and apply only the minimum that they required.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.