May 21, 2007 at 1:46 pm
On our production SQL 2005 servers I want to give developers readonly access to each user database and also give them the ability to see stored procedures. Readonly is handled through db_datareader, but how do I give them the ability to see stored procedures without granting permission to execute them?
Thanks, Dave
May 22, 2007 at 6:13 am
In 2005 you can just do this on the storedprocedures you want them to view.
use
[AdventureWorks]
GO
GRANT
VIEW DEFINITION ON [dbo].[uspGetEmployeeManagers] TO
GO
May 22, 2007 at 6:22 am
Go to the database, expand down to users, click on the user you want to give this permission, double click. On the bottom of that window there is a list named "Explicit permissions for [login name]:
Check the Grant box for view definition.
Should be able to do that for a group for the developers. This should set view definition for an entire schema, not one store procedure at a time.
May 22, 2007 at 7:00 am
Thanks. I found it. Two questions. When I want to remove the Explicit Permission is there a way to list only those objects where explicit permissions have been granted? I'm only able to list all objects, whether or not explicit permissions have been granted to the object.
Second, is there a way to grant an explicit permission to all objects of a specific type without having to select each one individually? I'm sure I can script this, but I was hoping to find it in the GUI.
Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply