Built-in db_execute right

I noticed you are taking suggestions for SP2 release of SQL 2005. One thing that would be nice would be a built in security role of db_execute that would give execute rights to all stored procedures in a database and/or db_executeUser to execute only user stored procedures in a database. It would simplify releasing for me. I always seem to forget to give the user account rights to execute everytime I add a new stored procedure.

Thanx,

I second the request..

Brien King
brien@.classic-soft.com

|||

Actually, there is a way to do this in 2005. I missed it through several passes of learning the security infrastructure and it's a pretty neat capability.

The first thing to understand is that you grant permissions on a securable to a principle. Once you've gotten there, it gets interesting when you realize that a Database User is a principle and a schema is a securable. So, if you were to put all of your procs into a single schema and then grant execute permissions on the schema to the user, they would be able to execute all of the procs in that schema. Now going one step up, you can also grant execute permissions on a database which means that any proc within the database can be executed by the user that you granted permission to.

Run the following example.

1. Connect to an instance as the sysadmin

2. Create a new login (for this example mine will be called test and will be a SQL Server login)

3. Add this login to the AdventureWorks database without any permissions

4. Open a new query window with a connection using the login created in step 2

5. Run exec AdventureWorks.dbo.usp_GetManagerEmployees 3

6. You should get a permissions error

7. Open a new query window and connect it as sysadmin (technically database owner authority which is the same account in this case). Switch database context to AdventureWorks.

8. Execute the following statement GRANT EXECUTE ON SCHEMA::dbo TO test

9. Now switch back to the other query window and rerun the proc. You should now get a result

10. But, if you try to execute any proc in the HumanResources schema, you will get a permissions error.

11. Now switch back to the sysadmin connection. Execute: REVOKE EXECUTE ON SCHEMA::dbo TO test

12. Verify that you no longer have permissions

13. Now execute GRANT EXECUTE TO test within the sysadmin connection

14. Switch back to the other connection. Re-execute the procedure and verify that you can now execute it. As a matter of fact, you can execute any proc in any schema within the database at this point

So, there really isn't a need to a database role to accomplish this, because it is already there. In fact, this capability can also be done with SELECT, INSERT, UPDATE, and DELETE, so it really should be used instead of db_datareader and db_datawriter. (I would expect to see these roles disappear in the future.)