April 25, 2007 at 8:50 am
I have a replication system that we are using as an inquiry only system to take the load off of the prod system. As part of that, I set up users as db_readonly rights. Some of the users and the application logins need to be able to execute stored procedures. I have a script that I run that grants this right, and need to go in and modify it every time a new stproc is added. I have found some scripts that I can run that will also work but the problem comes in when I need to create a new snapshot of changes to the stored procs blows away all previously created permissions as the stored procs are removed and recreated. If I forget to run the permissions scripts then problems ensue, and our envirornment at times can have many changes during the day to some stored procs as we rush to meet changing customer demands.
I would like to create a new role I can assign to the users that would give them db_readonly and the execute only rights that are part of db_owner and having problems finding the best way to work with this. This way any stprocs on the database are automaticly allowed to be executed without having to remember to run a script or add a new proc to the list. Has anyone had any luck with this? Am I approaching this the wrong way?
April 25, 2007 at 3:52 pm
In SQL Server 2005, you can do this:
1. Create a new database role
2. Add the user account to the role
3. Using SQL Server Management Studio, expand Security, Schemas under the database.
4. Assuming that your stored procs are in the dbo Schema, right click dbo and choose Properties.
5. Select Permissions in the left pane.
6. Add the role you created
7. In the bottom pane, check Grant next to Execute with grantor dbo.
This would allow any account in the role execute permission on existing and new stored procs.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 26, 2007 at 6:30 am
Thank you! I think the step I was missing was the use of the schemas to control the rights! This should work!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply