Grant Exec to all stored procedures

By CSharpner · November 24, 2009

In Microsoft SQL Server, if you want to grant exec authority to all stored procedures in a database to an individual account, you may have a lot of manual labor ahead of you fully prone to error, or you could automate it.  Here’s a stored procedure that’ll do it for you.  You just pass in the name of the SQL Server user account.

CREATE procedure .(@Executioner varchar(200)) as

begin

 -- From: http://csharpner.blogspot.com/2009/11/grant-exec-to-all-stored-procedures.html

 declare @ObjectName varchar(200)

 DECLARE ObjectCursor CURSOR FOR

 SELECT   name

 FROM   .sysobjects

 WHERE   xtype = 'P'

 OPEN ObjectCursor

 FETCH NEXT FROM ObjectCursor INTO @ObjectName

 declare @Command varchar(500)

 WHILE (@@FETCH_STATUS  -1) BEGIN

 IF (@@FETCH_STATUS  -2) BEGIN

 set @Command = 'grant exec on ' + @ObjectName + ' to [' + @Executioner + ']'

 print @Command

 exec(@Command)

 end

 FETCH NEXT FROM ObjectCursor INTO @ObjectName

 END

 CLOSE ObjectCursor

 DEALLOCATE ObjectCursor;

end;

Here’s how you call it, assuming you want to grant exec to a user called “db_user”:

exec GrantExecTo ‘db_user’

This stored procedure searches the system tables for all stored procedures, then builds multiple “grant” statements for each one and executes each one for you.  For your convenience of reviewing the results, it prints each generated command to the output window, so that if there are any errors, you’ll know which generated statement triggered it.