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%’

June 12, 2009 Posted by | Permissions | , , , | Leave a comment