Problem: You’ve transferred or run a bunch of stored procedure scripts, but you can’t execute them. Reason - execute permission denied. You forgot to put a grant statement in your script.

Solution: The trivial solution is, of course, GRANT EXECUTE ON {your proc name} TO PUBLIC. Slightly less trivial is to grant to a specific role, but most people needing this tip will only be using PUBLIC.

Wouldn’t it be great to automate this for all stored procedures in the database? Well, here you go:

DECLARE procs CURSOR FOR select [name] from sys.objects where type= 'p'
DECLARE @name as varchar(250)
DECLARE @stmt as varchar(1000)

OPEN procs

FETCH NEXT FROM procs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

     SET @stmt = 'GRANT EXECUTE ON ' + @name + ' TO PUBLIC'
     EXEC(@stmt)

     PRINT @stmt

     FETCH NEXT FROM procs INTO @name

END

CLOSE procs
DEALLOCATE procs

Posted with : Tech, Microsoft SQL Server and other databases, SQL Server