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.
| 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 |
|
|
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 |
 |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Create readonly user group