Role permissions

Question

Role permissions

oskargunn

SSCommitted

Points: 1951
More actions
May 4, 2011 at 10:49 am

#250154

Hi,
I have a question about roles and permissions.
Let´s say we have a role called MyTestRole and there are some members who have access to this role. The role even maybe has select permissions on some tables in the database.
Is it possible to grant db_datareader access to that role, so that each member of MyTestRole also gets the db_datareader permission without assigning it to all the individual users?
Something like this: grant db_datareader to MyTestRole ( i know this syntax doesn´t work; just wondering what is the best thing to do )

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes, absolutely correct. roles are cumulative, so if i belong to two roles, i get access to whatever was granted to each role.

grants are cumulative, and deny's/revokes top dog: if you have a role that takes away permissions, it will override other roels which granted the same permissions.

Actually the recommended practice is NEVER grant permissions to individual users, create a role for the permissions you wanted for that user, and add them to it. That's because typically people may come and go, but the job/access they needed will be required again.

here's a simple example:

CREATE ROLE [ReallyReadOnly]

--give my new role READ permission to ALL tables

EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

--explicitly DENY access to writing

EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

--should I give my new role permission to run the procedures you've created?

--GRANT EXECUTE TO [ReallyReadOnly]

--Should I allow the users to see view proc and function definitions?

--Grant View Definition ON SCHEMA::[dbo] To [ReallyReadOnly]

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!

oskargunn SSCommitted Points: 1951 More actions · Answer 2

Thank you for your reply. Although I still get some issues.

These are the commands I issue:

create role ReallyReadOnly

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReallyReadOnly]

ALTER AUTHORIZATION ON SCHEMA::[DB_DenyDataWriter] TO [ReallyReadOnly]

exec sp_addrolemember 'ReallyReadOnly', 'testari'

Then when I try to do a select command on a table within that database I get the following error:

The SELECT permission was denied on the object 'Log', database 'DBLog', schema 'dbo'.

Lowell SSC Guru Points: 323518 More actions · Answer 3

oskargunn my apologies; i pasted the wrong script from my collection; I've edited the example above, which should do what you wanted for your example.

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!

oskargunn SSCommitted Points: 1951 More actions · Answer 4

Could there be something that is missing?

I issued your updated statements and also this one:

exec sp_addrolemember 'ReallyReadOnly', 'testari'

Then I log on as 'testari', and select from the table Log, and I still get the same error I got before :/