Welcome

Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein



Monday 4 May 2009

Create a script to grant stored procedure permissions

This script creates a script which allows stored procedure permissions to be granted to the public role

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

The output below is the scripts to run e.g.

GRANT EXECUTE ON usp_insert_user TO PUBLIC
GRANT EXECUTE ON usp_insert_address TO PUBLIC

No comments:

Post a Comment

Note: only a member of this blog may post a comment.