What level of SQL Server access is required to view, but not execute, stored procedures and their code?
metadata, permissions, sql-server, stored-procedures
Solution
Admittedly, I'm not the most knowledgeable when it comes to permissions. However, I believe the minimum permission you need here is `view definition`. You can also grant this permission to a role if it makes more sense for your situation.
use MyDB
GO
GRANT VIEW DEFINITION to MyUser
The above will grant view_definition to `MyUser` for the `MyDB` database.
Changing to the following will grant `view definition` on any database:
use master
GO
GRANT VIEW ANY DEFINITION to MyUser
Sources:
https://msdn.microsoft.com/en-us/library/ms345443.aspx#Security http://www.mssqltips.com/sqlservertip/1593/granting-view-definition-permission-to-a-user-or-role-in-sql-server/
Problem
I have been granted `db_datareader` access to our production SQL Server database, but they also granted me the `denywrite` permission, as a safety precaution to make sure I absolutely cannot break our services during the course of my investigations. However, I am finding that I cannot see our stored procedures - the list appears empty. We should have hundreds of stored procedures in our production environment, so I'm perplexed as to why they aren't showing up in the object explorer. Our infrastructure manager granted me the rights, but he doesn't know anything about SQL Server, so management has asked me to assist with figuring out which SQL Server permissions I need, since I am the developer. So I need to know what I'm missing here - I assumed `db_datareader` would let me view everything, including stored procedures and metadata, but apparently I was mistaken. :)