File this under the “I can’t believe there is still stuff that I keep learning about SQL Server 2005!” though thankfully most things I find I learn are things I wouldn’t be all that likely to use.

I was asked today how I felt about using the syntax:

GRANT EXECUTE TO [username]

to give users rights to all procedures in the database (and you could use it with SELECT, INSERT,…and other rights too).  Well, first off, I had to admit that I didn’t know you could do this. I knew you could grant execute rights to a user on a schema, but not the entire database. Jasper Smith had an article back in 2004 on sqldbatips that covered it (http://www.sqldbatips.com/showarticle.asp?ID=8), so it isn’t some big secret.

I often use the technique to grant a user all rights to a given schema:

GRANT EXECUTE on SCHEMA::schemaName TO [username]

I often use the this technique to apply execute rights to an application login/users to a given schema. I don’t like it as much when using the dbo schema, since it commonly contains other object that I don’t want to just give blanket rights to, but when using named schemas to segregate objects into functional groups (partially for security, and partly for logical separation) I feel it is a good idea.

Even using the dbo schema isn’t horrible, as long as you understand what you are doing and are careful to separate out other procedures. I am a big believer in having the database be as self contained as possible, so I try to put maintainence objects and such in the database, typically in a schema named utility. Often this might have a procedure to drop all foreign keys, or indexes, etc.  Whatever I might need during a data load operation, or even just general maintenance. So granting rights to the entire db seems a bit too lenient as I don’t want ANYONE who isn’t the dba running these procedures.

I guess the fact is that I think that the database security should be a bit more stringent than a simple GRANT all rights to EVERYTHING, as you have to be cognizant that sometimes there will be objects in the database that just shouldn’t be opened up for the programmer to accidentally use, thinking that an object does something different than it actually does.  When you carefully lay out schemas, odds are that you are considering the purpose/meaning of the schemas and have  a plan for the schemas which SHOULD consider security/usage as well.