Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

GrantExecAllUserAllSpandfunctions.sql

By @fracionero,

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

Total article views: 883 | Views in the last 30 days: 3
 
Related Articles
FORUM

Error 229: Execute permition denied on object.............

Error 229: Execute permition denied on object.............

FORUM

Execution Error

Execution error while executing the procedure

FORUM

ERROR EXECUTING PROCEDURE on SQLS2K

WHAT A STUPID ERROR EXECUTING PROCEDURE..... BUT IM NOT GETTING...

FORUM

Error when Split function is executed..?

I get the following error when executing a newly created function...

FORUM

Rights to execute stored procedure

Rights to execute stored procedure

Tags
security    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones