GRANT VIEW DEFINITION

Earlier today I was asked to elevate the permissions of a user who already had read only access to a database so they would be able to see stored procedures and other programmable objects it’s something that comes up every so often so worth while writing a few lines as it will undoubtedly help somebody out.

For this scenario the permission you will need to GRANT is VIEW DEFINITION. As with all permissions this can be set at various levels for the intended user/role so I will outline the main headlines.

In the first example all metadata across the entire instance will be available to everyone (I am not condoning this, it’s just an example) :
GRANT VIEW ANY DEFINITION TO Public;

The second example will allow the public to see all metadata in the current database:
GRANT VIEW DEFINITION TO Public;

In the third example we will add the type clause which will limit the metadata to a particular schema, in this case dbo:
GRANT VIEW DEFINITION ON SCHEMA::dbo TO Public;

If we wanted to grant viewing rights on a particular object such as the imaginatively titled usp_Dummy stored procedure we would use the above code but change the SCHEMA keyword to the OBJECT keyword:
GRANT VIEW DEFINITION ON OBJECT::usp_Dummy TO Public;

In accordance with Microsoft’s policy of least privileges I would not recommend granting rights to public. The administrator should instead be using roles or Windows groups instead.