Description

This script allows granting permissions to execute to all users on all stored procedures and functions on current database.

The script have two cursors one for user in database and another to stored procedures and functions.

In first cursor, you can modify select statement to add filter on users, for grant only for a user individually or user into in clause.

select name from sysusers where hasdbaccess=1 and name not in ('dbo')

In second select statement cursor, you select all procedures and functions in database, for compatibility with db's without schemas, or objects without schemas.

select isnull(b.name,'dbo') +'.'+ a.name from sys.objects as a join sys.schemas as b on a.schema_id=b.schema_Id where a.type='fn' or a.type ='p'

For error control, body second cursor has enabled a block type try catch. When execution order fails we get a more detailed message error that system offer.

print ('Error on execution ' 
 + @spname + ' - ' + 
 convert(varchar(5),ERROR_NUMBER()) + ' - ' + 
 convert(varchar(5),ERROR_SEVERITY()) + ' - ' + 
 convert(varchar(5),ERROR_LINE()) + ' - ' + 
 convert(varchar(150),ERROR_MESSAGE()))

The errors results are as these:

Error on execution dbo.ufnGetAccountingEndDate - 102 - 15 - 1 - Incorrect syntax near ']'.
Error on execution dbo.ufnGetProductDealerPrice - 102 - 15 - 1 - Incorrect syntax near ']'.
Error on execution dbo.ufnGetProductListPrice - 102 - 15 - 1 - Incorrect syntax near ']'.
Error on execution dbo.ufnGetProductStandardCost - 102 - 15 - 1 - Incorrect syntax near ']'.
Error on execution dbo.ufnGetStock - 102 - 15 - 1 - Incorrect syntax near ']'.

At last we get a report to send to requester, with all execution permissions on the objects type procedures and function in database. Also you can modify the select to get only the results that you need.

select 
 convert(varchar(25),u.name) as 'UserName', 
 'Permissions to' = case WHEN p.action = 224 THEN 'EXECUTE' end,
 'Object Type'= case when o.xtype='P' then 'Stored Procedure'
 when o.xtype='FN' then 'Function' end,
 convert(varchar(100),o.name) as 'Object Name'
from sysprotects p
inner join sysusers u on p.uid = u.uid
left join sysobjects o on p.id = o.id
WHERE p.action = 224

Outcome:

Users have granted permissions on these objects

UserName Permissions to Object Type Object Name
------------------------- -------------- ---------------- -----------------------------------
fr EXECUTE Stored Procedure sp_test
fr EXECUTE Function ufnGetAccountingEndDate
fr EXECUTE Function ufnGetAccountingStartDate
fr EXECUTE Function ufnGetDocumentStatusText
fr EXECUTE Stored Procedure uspGetBillOfMaterials
fr EXECUTE Stored Procedure uspGetEmployeeManagers