Backup & Restore

Granting View Permissions in 2005

ever have a case where a developer needs to see the source code
behind some procedures, tables, or views on the production databases?

well instead of receiving 100 requests to send the object definitions of
these objects to them you can simply GRANT them permissions to
VIEW definitions.

here’s one example to grant a user permissions to see all definitions
of all Tables, Views, and Procedures of a particular database.

this will produce a single script for every object in the database. you
can use just one script per file, or run it all at once.

remember to set the output to text by pressing CTRL+T


use MyDatabase
set nocount on
‘use [MyDatabase] ‘ + char(10) +

‘GRANT VIEW DEFINITION ON [dbo].[‘ + name + ‘] TO [DOMAIN\User]’ + char(10) + ‘go’ + char(10)
from sysobjects where xtype = ‘P’ or xtype = ‘U’ or xtype = ‘V’ or xtype = ‘X’


some developers need to see the definition of particular database objects in production so they
can create those objects under their test/development platforms.

well normally when you think about it… you don’t want to perform grants per object. that would take
for ever, and the developers would constantly be coming back for more objects.

one solution is to just give them dbo which is not warranted right so the next best thing is just
give them the ability to see the definition wihout granting them any extra permissions. so you
give just the ‘view definition’ permission per object.

thats it.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s