Post reply

Role permissions

  • 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 )

  • May 4, 2011 at 10:56 am

    #1319928

    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!

  • May 5, 2011 at 3:52 am

    #1320286

    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'.

  • May 5, 2011 at 6:39 am

    #1320328

    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!

  • May 5, 2011 at 10:42 am

    #1320545

    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 :/

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

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