June 14, 2006 at 9:48 am
I think I am misunderstanding something
When I set up a user with read permissions they can read tables and do selects. If I also give them public permission they can then hit stored procedures. Well since they have public permissions can they also do inserts using the procedures? I think I may have a flaw in my thinking. I thought I was just giving read permissions.
June 14, 2006 at 10:22 am
Depends on what permissions have been granted to Public, which is a database role. If Public has 'Execute' permission for a stored procedure, the stored procedure does not contain dynamic SQL, and the stored procedure and tables are owned by the same user, the user would be able to insert via the stored procedure.
Greg
Greg
June 14, 2006 at 10:35 am
Yup all those things are what I am doing. So I need to grant execute rights only to specific Procs. Is there a good way to handle this? I have many procedures. I would hate to have to do this manually. They are prefaced with
Proc_Select_..
Proc_Insert_..
Proc_Delete_..
Proc_Update_..
Everything I have is maintained from groups. Can I say SQLRead.... group only gets Proc_Select_... and not Proc_Update... automated wise
June 14, 2006 at 10:44 am
The best practice is to create user-defined database roles within your SQL Server database. The roles should correspond to, well, roles. For instance, in a help desk database, you might have roles such as: help desk rep, help desk manager, normal user, normal manager. The help desk rep would have read and write access to most objects, the help desk manager would have all the rights of a help desk rep + some, a normal user might be able to retrieve information related to current tickets, whereas a manager might be able to access the archive tables. This breaks away from one role for read, one role for insert, one role for update, etc., because it fits in better with how your business operates.Figure out how these breakdown and then assign permissions to the user-defined database roles as appropriate. Then you take your groups (I assume you mean Windows security group), assign them to the appropriate security roles (and they can be assigned to multiple roles, if necessary), and your security should be setup.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply