2

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.

CREATE, SELECT, UPDATE, DELETE, ALTER

Question: What minimal permissions 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?

Update: Please note that we are not allowed to use built-in database roles such as db_datawriter, db_ddladmin as these would give more permissions to a user than it's needed). Ref

1
  • A user with those permissions might as well be a DBA with CONTROL permission over the database. Sep 13 at 17:26

2 Answers 2

1

If you do not need those permissions, only the MyDbRole needs them - your DBA can grant permissions to this role directly, without you being the man in the middle, by running below code:

use [YourDBName]

GRANT CREATE TABLE TO [MyDbRole]   
GRANT ALTER TO [MyDbRole]   
GRANT SELECT TO [MyDbRole]      
GRANT UPDATE TO [MyDbRole]   
GRANT DELETE TO [MyDbRole] 

If you need those permissions as well, your DBA can grant it to you with grant option, so you can grant permission to your role then:

use [YourDBName]

GRANT CREATE TABLE TO [YourLogin] with grant option 
GRANT ALTER TO [YourLogin] with grant option   
GRANT SELECT TO [YourLogin] with grant option      
GRANT UPDATE TO [YourLogin] with grant option   
GRANT DELETE TO [YourLogin] with grant option 
2
  • Your suggestion worked (thank you). But when I try to revoke CREATE TABLE from that YourLogin, the same YourLogin can still create a table. I have posted a new question here
    – nam
    Sep 13 at 18:40
  • You deserve an upvote. Done.
    – nam
    Sep 14 at 16:27
0

There is not need to create the database role.

You just need to add the user to the following 3 roles:

-- db_datareader role will provide SELECT permissions on all tables

EXEC sp_addrolemember 'db_datareader', 'YourUser';

-- db_datawriter role for DELETE, UPDATE and in addition INSERT

EXEC sp_addrolemember 'db_datawriter', 'YourUser';

-- db_ddladmin role to create and alter objects

EXEC sp_addrolemember 'db_ddladmin', 'YourUser';

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.