1

I want to grant a user defined role, read and write permissions to the entire database and add users to it. I was unable to do this using GUI. So I tried the solution given at the link - https://www.sqlservercentral.com/Forums/Topic1141208-1526-1.aspx but it gives me the error mentioned below when I try to add db_datawriter to the role I created.

Cannot use the special principal 'db_datawriter'

The command I executed - EXEC sp_addrolemember 'DR_User', 'db_datawriter'

Please help me figure out the right way to achieve this.

2
  • It seems you are doing the opposite to desired. You are trying to add role [db_datawriter] to [DR_User] principal.
    – i-one
    Mar 29, 2017 at 12:00
  • Thanks for the response @i-one. That was suggested in the article I have mentioned in my question. Is there a way to grant read and write permissions to a user defined role other than adding the role as a member of fixed roles?
    – JKay
    Mar 29, 2017 at 12:22

1 Answer 1

3

JKay, you are a little mistaken about the syntax of this command:

EXEC sys.sp_addrolemember [ @rolename = ] 'role',  
    [ @membername = ] 'security_account' 

So in your particular case the command is:

EXEC sys.sp_addrolemember @rolename = 'db_datawriter', @membername = 'DR_User';
1
  • Hi @SmokingDBA, My understanding of the syntax is correct but my idea is wrong. I just realized that. Thanks :)
    – JKay
    Mar 29, 2017 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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