Grant exec on all stored procs

Its so annoying that sql server doesn't come with a default role to just have exec permissions on all stored procs. Oh well.

I love this little stored proc. Its usually a good idea to prevent your database user from having direct read/write access to your database to tricks some nasty hacker might throw at you - Its usually good practice to give your user just access to the stored procedures. I usually run it at the end of every release to ensure the user can exec any stored proc.

use master
go
create procedure sp_grantexec(@user sysname,@pattern sysname = NULL,@debug int = 0)
as
set nocount on
declare @ret int
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(@user)


set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

if @pattern is not null
set @sql = @sql + N' AND ROUTINE_NAME LIKE ''' + @pattern + ''''

if @debug = 1 print @sql
else
exec @ret = master.dbo.xp_execresultset @sql,@db

If @ret <> 0
begin
raiserror('Error executing command %s',16,1,@sql)
return -1
end