September 16, 2008 at 9:07 am
In my environment, we have a custom site that allows QA to execute a developer's stored procedures and view results via a web form submission. However, stored procedures can be added at any time, and QA should have "instantaneous" access to newly added stored procedures.
Does a role exist that allows the role's users to execute stored procedures only? If not, is there any way to automatically update the user's permissions whenever a developer writes a new stored procedure?
September 16, 2008 at 9:15 am
I don't think there's a way to do this. I thought someone had done a GRANT EXECUTE on a schema, but not sure if that would work.
The thing to do is be sure that the GRANT EXECUTE to is at the end of every stored procedure statement. I've always kept the security with the source code to keep track of it. Developers should be able to add this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 16, 2008 at 9:22 am
I don't know if DDL triggers can be used for that...
but you could always have a job run every hour to see if new procs have been created... then set permissions and shoot and e-mail to someone.
The thing here is to make sure that the proc is completed... and not still in developpment.
September 19, 2008 at 8:40 pm
Giving the 'execute' permission on that database for the user should fix the problem.
September 19, 2008 at 10:06 pm
Create a role in the database, grant execute privileges on the appropriate schema to the role and add your users to the role. To grant execute privileges you would use:
GRANT EXECUTE ON schema::dbo TO role;
The above grants execute privileges to the role for all objects in the 'dbo' schema. If you add a new procedure into the 'dbo' schema, the role will already have execute privileges.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply