April 14, 2005 at 9:51 am
I currently have a VB App using SQL as a backend. Currently, there are two types of users, those who read, and those who can update tables, contained in two NT User Groups, sqldpread and sqldpwrite.
I am adding a new table, "TPL", and different types of users will now be accessing the table as follows:
What would be the best way to handle this scenario?
April 14, 2005 at 11:54 am
Create two database roles, one for read and one for write. Map the NT user groups to those two database roles.
For updating the tables, if at all possible, control using stored procedures. Grant execute rigts to the database role you want for users that need to update. If you can't use stored procedures (can't change the VB app, for instance), then assign the appropriate update rights to the database role.
For running reports, again, if you are in control of how the data is reported back, use stored procedures. This can give you a performance gain due to caching of execution plans (this is also true on the update side, BTW). Again, grant all rights to the database roles.
If some users are in both Windows groups, they'll get both sets of rights. SQL Server will aggregate all permissions so long as there aren't any DENYs in the mix. DENY, of course, trumps everything. Otherwise, all permissions just roll-up for a user.
K. Brian Kelley
@kbriankelley
April 15, 2005 at 7:01 am
Thanks Brian. The data is accessed via stored procedures, so I think I'll go with your suggestion and map the user groups to database roles and give the roles execute permissions on the appropriate stored procs.
Pat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply