Security Questions: Grant Permissions to All Stored Procedures

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

StoreD Procedure question

The second question in the list is:

Do we have easy way to grant all stored procedures execution in a single shot?

When I was a DBA working on SQL Server 7 and SQL Server 2000, this was a near constant pain.  I wanted to give users the ability to only execute stored procedures and wanted the least amount of hassle while doing it.  Back then, that meant a script that would run nightly and verify that all stored procedures were included in the database role for executing stored procedures.  Needless to say, while automated, the delay between deploying the procedure and granting access often got the better of me and we really needed something different.

In steps SQL Server 2005.  At this point, you might be thinking, “are you going to tell me that I’ve been able to do this since SQL Server 2005?”  Yes, since SQL Server 2005, there has been a permission that allows execution for all stored procedures to be assigned to any principal.

Exec All Stored Procedure Demo

Granting the permission to execute all stored procedures is rather simple.  It just requires knowing the user (or role) that needs the permissions and a grant statement, shown in listing 1.

--Listing 1. Grant exec to all stored procedures in a database

GRANT EXEC TO [User]
GO

Of course, the question that immediately follows the permissions to grant all stored procedures is one where the users need access to all of them, except these other ones.  There are a few ways this can be approached.  One useful manner to control the permissions is to scope the permissions to a specific schema.  The GRANT option, shown in listing 2, can allow the user to only execute stored procedures in the schema.  Or the user could be granted permissions for all schemas, as was done previously, and then the schema that shouldn’t be accessed has DENY permissions set.

--Listing 2. Permissions based on executing stored procedures in a schema

GRANT EXEC ON SCHEMA::SomeSchema TO [User]

DENY EXEC ON SCHEMA::AnotherSchema TO [User]
GO

There are numerous ways to achieve this, but I’ve always found it useful to know that you can scope permissions, such as EXEC, to the schema.

Summary

If you weren’t aware of the flexibility in assigning broad permissions to stored procedures, this post has hopefully enlightened you to some new security options.  Are there any permission combinations that you’ve tried to accomplish that haven’t worked out right?  Or, have you come up with an interesting permission solution that you think others might find useful?  Leave a comment and let us in on your solutions or problems.