What is the best way to allow a set of users permissions in SQL management console to be able to see and update stored procedures?
I have created a new DB role and granted "Execute" to that role. However, without adding additional rights, users can not see the SP's in management console. Again, the need here is to allow visibility to the SP's AND ability to update the SP's, while maintaining restrictions to things like update and alter tables.
The Sproc will run with the permissions (e.g. to SELECT / UPDATE / etc Tables) of the person that creates / alters the SProc ... so if someone modifies it, and that person does not have permissions on the underlying tables, then it ain't going to work.
There are ways around this - the SProc can EXECUTE AS or have a signed certificate for the user permissions it needs - but that is not the norm, and I suspect, even then, that any user that modifies the code in an SProc is goign to need elevated permissions.
Nowadays the Owner, as was, and the Schema are no longer so tightly bound, so maybe?? it would be possible to have your Developers part of a Schema such that they can Alter SProcs without having permissions on the underlying tables? I haven't tried it, but I am doubting it ...
FWIW we never, ever, modify SProc code by using Edit in SSMS. We keep the code for each Sproc in a file on disk, and Load/Save that as we make changes. All the individual files are then stored in a Revision Control System (SVN in our case) so that we can revert-to, or compare-against, any previous version; we can also roll-out from DEV to Test and from Test to Production based on any version-marker in the SVN repository. Revision Control also takes care of two developers editing the same SProc / Source code "at the same time", rather than "first-to-save" being the winner and "second-place-loses-everything"
That won't help with managing permissions on the code though, although I suspect it would at least mean that DEVs could see the whole code-base in the File System (whereas you currently have the problem that DEVs cannot see all the Sprocs in SSMS)
Our Sproc scripts include a "Logging command" (so we have a history of the Sproc being created/altered) and the GRANT EXECUTE permissions to whichever ROLEs are applicable so that the Permissions are an integral part of the Source Code for our Sprocs.
Here is our way of letting the developers view the SP. We do not allow them to modify them only execute and view on the production server. (Note we are a small shop so execute is needed).
USE master;
go
GRANT VIEW ANY DEFINITION TO [domainname\Developergroup];
go
