<

Grant Execute On SQL Objects

Published on
11,128 Points
8,128 Views
Last Modified:
I wrote this script few years ago and I still use it a lot. It's very useful when I receive scripts to run in databases and after I need to give permissions in created objects as Stored Procedures and Functions.

IMPORTANT:
- Don't forget to set value for @UserName
- Don't use this script if the user you want to give permissions shouldn't have execute permission on ALL objects.

------------------------------------ BEGIN ------------------------------

declare @ObjectName varchar(255)
declare @UserName varchar(255)
declare @GrantStmt nvarchar(4000)

set @UserName = 'DBUserName_Here'

declare Objects cursor for
select name from sysobjects
where (xtype = 'P' or xtype = 'FN')
  and name not like 'dt_%'

open Objects

FETCH NEXT FROM Objects INTO @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN
      set @GrantStmt = N'grant execute on ' + @ObjectName + ' to ' + @UserName
      EXEC SP_EXECUTESQL @GrantStmt
      FETCH NEXT FROM Objects INTO @ObjectName
END

close Objects
deallocate Objects

----------------------------------- END -----------------------------------
0
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free