Oftentimes, when I’m creating stored procedures, I forget to grant execute permissions to my user(s). Here is a stored procedure that will do this for you by simply passing in the username or a comma delimited list of users. Want to revoke permissions? Replace GRANT with REVOKE!
CREATE PROCEDURE GrantExecuteForUser(@user varchar(30))ASdeclare @sql varchar(255)declare grant_cur cursor forselect 'grant execute on ' + name + ' to ' + @user as sqlfrom sysobjectswhere xtype = 'P' AND name not like 'dt_%'order by nameopen grant_curFETCH NEXT FROM grant_cur INTO @sqlWHILE (@@fetch_status <> -1) BEGIN exec(@sql) FETCH NEXT FROM grant_cur INTO @sqlENDclose grant_curdeallocate grant_curgo