Post reply

New Database role not adding 'db_datareader' as a securable

  • September 30, 2010 at 7:06 am

    #142100

    hi All,

    I have created a new database role "myNewRole" and wish to it to have select permissions on all tables and have grant persmissions to insert, delete ,update on several individual tables etc. I can add these ones no problem but when adding 'db_datareader' as a role i.e to get all select permissions I get error message (this is even for any other fixed roles)

    Cannot find the object 'db_datareader', because it does not exist or you do not have permission.

    The corresponding sql is

    GRANT CONTROL ON ROLE::[db_datareader] TO [myNewRole] in scripter

    any help guys ?

  • October 4, 2010 at 4:32 am

    #1230892

    hi All,

    been playing with this for a while...

    trying to add an existing fixed role to a new user defined role should work

    exec sp_addrolemember @rolename = 'db_datareader', @membername='myNewRole'

    but it does not and or trying to add 'db_datareader' as a securable does not work either...

    but the following seems to

    GRANT SELECT ON Schema::dbo to [myNewRole] can be added by this TSQL

  • October 4, 2010 at 6:18 am

    #1230938

    this is how i usually create a role and then add various built in roles to it;

    I hope this helps

    CREATE ROLE [MyRole]

    ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [MyRole]

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

    ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [MyRole]

    GRANT EXECUTE,ALTER TO [MyRole]

    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!

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

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