User defined database role

Question

Post reply

User defined database role

jayoub

SSCertifiable

Points: 6991
More actions
December 16, 2016 at 7:30 pm

#319762

I am trying to create a user defined database role that gives an SQL login the following:
Db_datareader
Db_datawriter
Execute on all stored procedures
I am using SQL server 2014
I create the role in the database and added the SQL login as a member and in the securable part I added the database and checked the box “Execute”
My question is how do I give that user defined database role the db_datareader and db_datawriter permissions.
I did find where I could add these two in the securables, but did not know what explicit permission to select or if I should even add them in the securables. My choices where Control, alter, and a few others that I do not remember now.
Currently the SQL login is a member of the db_datareader and db_datawriter, but I was hoping to create the user defined role give it all the privileges it needs, add the sql login to that and remove it from the fixed database roles.
Also does anybody know about a book that would have this information
Let me know what you think – your help is appreciated.
Jeff

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I tend do do commands like this in a script rather than the GUI;

my example script would look something like this:

CREATE ROLE DevelopersReadWrite;

GRANT VIEW DEFINITION TO [DevelopersReadWrite];

GRANT EXECUTE TO [DevelopersReadWrite];

EXEC sp_addrolemember 'db_datareader','DevelopersReadWrite';

EXEC sp_addrolemember 'db_datawriter','DevelopersReadWrite';

To do that from the GUI, you have to ADD the role first,

and then separately go to the role db_datareader, and then in the membership section at the bottom, click ADD button and add the new role you created.

Then you have to do the same to the role db_datawriter...open it and click ADD.... and find the role.

Unlike adding a user, the permissions screen for a role is not a one-spot resource, you have to bounce around.

also the GUI will show you effective roles for the users...that seems a bit weird.

so when you go and look at myDomain\Bob, who only was added to the DeveloperReadWrite role, when you look at his permissions in the GUI, it will say he is in the roles for db_datareader and db_datawriter, even though the membership came from another role, and you did not explicitly add him to those roles.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jayoub SSCertifiable Points: 6991 More actions · Answer 2

Thank you very much for the help. I have marked it as solution

Jeff