Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

New Database role not adding 'db_datareader' as a securable Expand / Collapse
Author
Message
Posted Thursday, September 30, 2010 7:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
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 ?
Post #995894
Posted Monday, October 4, 2010 4:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
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








Post #997438
Posted Monday, October 4, 2010 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #997488
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse