able to add the roles db_datareader and role db_datawrit

Question

able to add the roles db_datareader and role db_datawrit

samirca007

SSC-Addicted

Points: 417
More actions
January 24, 2020 at 3:52 pm

#3720198

Hello,
I am asked to configure a user so that he can create users himself and assign them the roles db_datareader and role db_datawriter.
At the instance level, I assign the securityadmin role.
At the database level, I assign the roles db_accessadmin and db_securityadmin (+ db_datareader and db_datawriter)
this is not enough...
I read on several forums that you must be db_owner:
It seems excessive to me ...
Could you confirm or deny me this info and if necessary tell me which right (s) to add?
Thank you in advance,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Sounds right to me. From the documentation:

Fixed-database roles are defined at the database level and exist in each database. Members of the db_owner database role can manage fixed-database role membership.

John

crow1969 Hall of Fame Points: 3001 More actions · Answer 2

You can get around this, by creating a role, and adding that new role as a user to the db_datareader group. Then your user can add members to the new role, and they inherit db_datareader.

While I do not see anything about this in the documentation, it may be that non-dbo users can alter the standard database roles.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Ah yes, that's probably why in the link I posted, they recommend not to make user-defined roles members of fixed roles!

John

crow1969 Hall of Fame Points: 3001 More actions · Answer 4

OK. Found the relevant bit here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

db_securityadmin

Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.

The key bit is "Can modify role membership for custom roles only".

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

If you think granting db_owner to someone is excessive, then you probably shouldn't be granting them SecurityAdmin role at the instance level. Microsoft's documentation on that says it should be considered the equivalent of SysAdmin:

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2014#fixed-server-level-roles

While SQL Server prevents a SecurityAdmin from granting SysAdmin permissions, it does allow it to grant CONTROL SERVER permissions, which can lead to other security escalations.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Another alternative might be to create a signed stored procedure(s) that add users to the roles instead of depending on using SSMS for the user to add members to those roles.

Sue