How do I grant EXECUTE permissions on all stored procedures in a database?

At ReDoc - Rehab Documentation Company, where I am employed, we distribute our software to thousands of sites and once in a while we would get a support call and after tracking it down we found that our standard user had no permissions to execute this or that Stored Procedure.

So it fell to me to make sure that when our packages left our walls that our standard user would have these permissions.  In that effort I developed a SQL that would grant EXECUTE permission to a certain user for ALL Stored Procedures in the database.

here it is:



-- Author: Thomas Ross
-- Date: 10/20/2009
-- desc: Grant EXEC on all stored procedures to User X for the currently selected database.

declare @user varchar(100); set @user='ReDocUser'

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = @user)
begin

declare @st varchar(max)

DECLARE @name varchar(max)
DECLARE db_cursor CURSOR FOR

SELECT name From sys.procedures order by name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
set @st='grant exec on '+@name+' to '+@user
exec (@st)
print 'Granting EXEC on '+@name + ' to '+@user

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
end
else
print 'The user specified does not exist.'

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.