GRANT Schema Permissions on all Store Procedures.

Challenge:

Developer emailed me today and asked me to have db_owner access to production database.

Initial Reaction:

When I first saw this email, I said to my self “HELLO NO”

Solution:

I took a deep breath and emailed him and asked “What exactly are you going to do in production” His response was “I would like to view the script of Store Procedures” Then I decided to grant him that request. Below is the TSQL to grant view definition on schema.

use Database_Name
go
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [DOMAIN\user]
GO

 

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

 

Please do not go giving everyone db_owner in Production servers.

This solution satisfied both myself and the developer.