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
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
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.