New Database role not adding 'db_datareader' as a securable

• robinrai3

  SSCrazy

  Points: 2812

  More actions

  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 ?

• robinrai3

  SSCrazy

  Points: 2812

  More actions

  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

• Lowell

  SSC Guru

  Points: 323527

  More actions

  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!