Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Create readonly user group

Author  Topic 

ramaiah
Starting Member

27 Posts

Posted - 2011-02-04 : 15:05:58
Hi Team,

Instead of creating the new login for providing the access to the window user on perticular sql instance. I would like to create a readonly user group and add that window user to that group. Please help on this.

Thank you,
-Rams

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-04 : 15:09:02
You can use the built-in db_datareader database role to grant read access to your tables/views. Add your users to that role.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-02-05 : 00:12:17
Create an AD Security group for those users, add that security group to SQL Server. Then, create a role in SQL Server that has the specific rights and put the AD Security group in that role.

You can avoid the role and just apply the rights to the AD group, but if you have other AD groups that need that access it is easier to just add the security groups to the role.

Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-05 : 00:15:48
I only avoid the role if the read access is needed on specific objects. The benefit of the built-in role is that when new objects get created, the members of the role already have the read access. A new role with specific rights would not have this. So it just depends on what you need. I've always just used the db_datareader role for read access as the read access was needed on all tables/views.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-02-05 : 11:34:06
Tara - just to clarify my statement. I use db_datareader for most read only roles also, because in most cases the user will need to be able to read all tables.

I prefer having the AD/Security team managing users. By creating an AD security group - the AD team can manage adding/removing users to that group and all I need to do is add the security group to SQL Server. Then, we either grant appropriate rights to that login, or add that login to a specific role - depending upon what access is needed.

So, if all that is needed is read only access - I probably would just add that login to the db_datareader role. However, if these users need access to be able to update/insert/delete from certain tables and read only on all tables, plus execute rights to some procedures (but not all) - then I will create a role with those rights and add the login to that role.

Once setup, new users are added to the AD security group and they get the needed access to SQL Server. When that user is removed from the AD security group, the no longer have access. At this point, I no longer have to manage adding/removing users - that is all done by the security team.

Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-05 : 18:07:02
Oh, when I say user, I'm referring to an AD group or whatever you want to put in the role. I certainly do use AD groups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -