THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Granting rights to all objects in a database

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. 

Published Friday, March 13, 2009 12:38 AM by drsql
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

I had no idea about that one either.  Interesting...

March 13, 2009 9:19 AM
 

jerryhung said:

I have a trigger in some dbs to auto-grant EXECUTE permissions on any newly created procedures, to a DB role (not schema/user). It's interesting I'd say

-- Adds Executor role (for DB) to run SP's

EXEC sp_addrole 'db_executor'

CREATE TRIGGER Create_SP_Trigger

ON DATABASE

FOR CREATE_PROCEDURE

....

GRANT EXECUTE ON [dbo].xxx TO db_executor

....

March 13, 2009 12:48 PM
 

Ranga Narasimhan said:

Is this a bug or feature ?

March 16, 2009 4:42 PM
 

drsql said:

Um, I think I deleted a comment by accident :(  

Anyhow the question was: Is this a bug or a feature.

It is a feature...  From BOL, the syntax is:

Simplified syntax for GRANT

GRANT { ALL [ PRIVILEGES ] }

     | permission [ ( column [ ,...n ] ) ] [ ,...n ]

     [ ON [ class :: ] securable ] TO principal [ ,...n ]

     [ WITH GRANT OPTION ] [ AS principal ]

Note that ON is in brackets [], denoting that it is optional.  Very interesting, I must say

March 16, 2009 4:45 PM
 

Linchi Shea said:

I was reading Louis Davidson’s post earlier today, and what he said below caught my attention: “I am

March 16, 2009 5:57 PM
 

MontyMole said:

I do this in all our databases to create a role to use along with the db_datareader & db_datawriter roles

CREATE ROLE [db_executor];

GRANT EXECUTE TO [db_executor];

March 17, 2009 9:23 AM
 

Tim Mitchell said:

Count me among those who didn't know you could do this.  Thanks for sharing.

March 17, 2009 9:30 AM
 

drsql said:

I should note that overall I was fairly negative to the concept :)  

March 18, 2009 11:40 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement