July 13, 2011 at 11:53 am
Hello Fellas, I have created two roles Role1 and Role2. Role1 for read purpose and Role2 for Read and write purpose. It might be little stupid but can I assign db_datareader to Role1 and db_datareader and db_datawriter to Role2 ? Am i doing it right way or wrong ? Whats should be the right way ?
July 13, 2011 at 11:55 am
Generally speaking I beleive in using wiindows groups and then only assign the appropreate permissions to those groups.
July 13, 2011 at 12:56 pm
it can't be done in the GUI but SQL Server supports it
exec sp_addrolemember 'mygroup','groupToAddToMyGroup';
There are advantages to using roles, because sometimes you need to add multiple existing AD groups into a single role and assign permission to that role rather than N AD groups.
It's more flexible, too... but adds another layer.
Craig Outcalt
July 13, 2011 at 1:17 pm
I'm using windows group only. After creating database user based on two windows groups say sql\Group1 and sql\Group2, i want to assign roles Role1 and Role2 to them, simultaneously. Now Role1 should have read access and Role 2 should have Read plus Write access. So trying to assign db_datareader to Role1 & db_datareader and db_datawriter to Role2. How can I achieve it ?
July 13, 2011 at 1:32 pm
not quite sure what you're getting at, but here's my attempt.
create role role1 authorization dbo
create role role2 authorization dbo
exec sp_addrolemember 'db_datareader','role1';
exec sp_addrolemember 'db_datareader', 'role2';
exec sp_addrolemember 'db_datawriter','role2';
now you just need to assign the user into the roles as appropriate
exec sp_addrolemember 'role1','sql\group1';
etc.
search the BOL for sp_addrolemember for more details.
Craig Outcalt
July 13, 2011 at 1:38 pm
It cant get any better than the SQLBOT put it for you.
July 13, 2011 at 11:09 pm
Thanks SQLBOT. Actually I created some mess and had to clean it with Alter Authorization. Thereafter cleaning all the dirt. I started from teh scratch from your code. Probably what I was missing in mine was using sp_addrolememberto add those fixed db roles to my new created roles. Thanks once again. It was real great help. However few silly doubts:
1. If I'm granting db_ddladmin permissions to any user, will that user be getting db_datareader and\or db_datawriter permissions automatically ?
2. The users having db_datareader and db_datawriter roles will not ahve permissions to see procedures or execute them. How I can provide permissions to either check the code and/or execute on the procedures ?
3. How can I grant permissions to check execution plans ?
July 14, 2011 at 4:00 am
Anyone suggest on thsi:
1. If I'm granting db_ddladmin permissions to any user, will that user be getting db_datareader and\or db_datawriter permissions automatically ?
2. The users having db_datareader and db_datawriter roles will not ahve permissions to see procedures or execute them. How I can provide permissions to either check the code and/or execute on the procedures ?
3. How can I grant permissions to check execution plans ?
July 14, 2011 at 11:30 pm
Thanks for those queries SQLBOT. Just wanted to know if same will be the qqueries for SQL logins as well ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply