Menu
To grant permissions on a stored procedureFrom Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search. In Select Users or Roles, select Object Types to add or clear the users and roles you want.
To grant privileges for executing stored procedures and stored procedure packages: Issue the SQL GRANT statement with the EXECUTE ON PROCEDURE clause to the appropriate authorization ID or role. To grant the EXECUTE privilege to an authorization ID, use the GRANT statement with the EXECUTE ON PROCEDURE clause.
Even if your stored procedures are using SELECT statements, you won't be able to execute those procedures unless you've permission to execute. If you've READ permission on database, you can read data only from Tables, Views, and Functions.
Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to n stored procedures.
When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role.
Here are the steps to apply permissions:
Repeat the above for n objects. Fire up some music to keep yourself entertained while doing this for 100+ objects! There's got to be a better way! It's a clickfest of major proportions.
Question:
Is there a faster way to perform this task using SQL Server Management Studio 2005? Perhaps another GUI tool (preferably free)?
Any suggestions for creating T-SQL scripts to automatically perform this task? i.e. create a table of all stored procedure names, loop, and apply the exec permissions?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With