ummm I dunno… why can’t you see the script behind the stored procedure? I can see it just fine…. then again I’m sysadmin so if I can’t see it then there is definitely a problem.
Turns out… after all this time with working in SQL 2005 I still learn new things or at least things that I’ve long since forgotten. In production developers have db_datareader access and usually nothing more then that (it’s even more then I think it should be but I usually lose that battle depending on the sensitivity of the data). That being said why shouldn’t a developer have access to view the code or metadata behind the objects within a database? I can understand protecting the metadata from normal users but I can’t think of a good reason why developers should be locked out from this… Turns out that in SQL 2005 you need to actually grant access to do this.
If you want to mimick what was available in SQL 2000 the following 2 statements need to be ran:
GRANT VIEW ANY DEFINITION TO PUBLIC
This allows all server logins to view any metadata in any database
GRANT VIEW SERVER STATE TO PUBLIC
This allows all server logins the ability to see the DMV (Dynamic Management Views) which have replaced the SQL 2000 virtual tables like sysprocesses.
So access to metadata is a privilege by default in SQL 2005. I personally like the approach of meeting somewhere in the middle of SQL 2000 and SQL 2005. I think developers should have the same access and visibility they had in SQL 2000 but “public” shouldn’t be used. I would grant the access to a group something like this…
GRANT VIEW ANY DEFINITION TO DOMAIN\Group
GRANT VIEW SERVER STATE TO DOMAIN\Group
Click here for further information on View Definition Permissions
