Permission to grant SELECT, UPDATE, DELETE, ALTER on all tables

Question

In SQL Server 2019, I have created a user defined database role MyDbRole. Now I want to grant following permissions to 'MyDbRole' for all tables in the database. Question: What minimal permission I need to achieve this task. If I were to ask a DBA to give me a permission so I can grant following to 'MyDbRole', what permission should I ask a dba for. I'm guessing, it has something to do with WITH GRANT OPTION, but I am not quite sure as to exactly what permission I need?

CREATE, SELECT, UPDATE, DELETE, ALTER

Answer 1

There are already pre-defined database security roles for this:
SELECT: db_datareader
INSERT/UPDATE/DELETE: db_datawriter
CREATE/ALTER/DROP: db_ddladmin

See Database-level roles

Answer 2

Hi @nam wam ,

I have created a user defined database role MyDbRole.

Did you create your own flexible role here?

Note: Do not add flexible database roles as members of fixed roles, as this can lead to unexpected privilege escalation.

I've put the steps you might need below.
*-- 1. Create a login user (execute under the master database)
USE master
GO
CREATE LOGIN TestLogin WITH password='abc123456...';

-- 2. Create a database read-only user (execute under a user database)  
USE Test1  
GO  
CREATE USER TestUser FROM LOGIN TestLogin;  

-- 3. Add users to database roles, such as owner role db_owner, read-only role db_datareader, etc. (executed under a user database)  
USE Test1  
GO  
EXEC sp_addrolemember 'db_ddladmin', 'TestUser';  
EXEC sp_addrolemember 'db_datareader', 'TestUser';*  

In addition, if you don't want to use the method of adding fixed roles, you can also use the GRANT method. Such as GRANT privileges ON object TO user. Here is a related article on Method 2: https://www.techonthenet.com/sql_server/grant_revoke.php

Hope these would give you some help.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.