September 30, 2010 at 7:06 am
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
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
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy