Add Execute permission to a user.

There is no general execute permission that I am aware of, but you can assign execute permissions to a schema, which is then inherited by all SPs belonging to that schema.

In conjunction with your other question, you can query the INFORMATION_SCHEMA to generate SQL for granting the permissions on all SP:

SELECT 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO <USER>'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

Select allOpen in new window

What is the ROUTINE NAME ? Is it the stored procedure?

Does this statement apply to one SP on execution only? What if you have 90 SPs?  What is the routine to get into each one and add the permission to a user?

That's precisely the reason for assigning execute permission by schema instead of individually. You may need to move the SPs that you want to enable this way to their own schema, to avoid opening up access to other existing SPs that these users should not be able to execute. Or create new SPs in a new schema that simply call the other SPs in the original schema.

This query creates a row for each stored procedure in your database. You can then copy the results into another query window and run it.

OK, Megan I'm not familiar with your approach. First, I'd like to know how to create a schema to separate  or move the SPs  other than where they are now.

Shaun, if I understand correctly by reading and not applying yet I t appears that your solution  is two-step. Whatever the results are in the output, then copy and paste to a new query window and this will ultimately be the answer.

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

For more information have a look: https://stackoverflow.com/questions/9321334/grant-execute-to-all-stored-procedures

THANKS