The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

Grant Permissions script


/*this script will give you execution statements for all objects under your DB. Simply replace the DbAdmin with the user you want to give permissions to and that is it.*/

set nocount on

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘U’

and       name not like ‘dtp%’

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘V’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘P’

and       name not like ‘dt%’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘FN’

and       name not like ‘dt%’

Advertisements

June 12, 2009 - Posted by | Permissions | , , ,

No comments yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: