Grant db_dataExecute

Posted: June 29, 2010 in SQLServerPedia Syndication

Have you ever wanted to grant a user account the ability to execute permissions in a database without having to worry about maintaining object level permissions? Maybe we should back up a bit… When you have an account that needs to read data only, it is easy enough to add them to the db_datareader database role. If you want to allow that same account to update the tables directly then db_datawriter is the role you need to add the account to. But what do you do if you need to grant execute permissions to an account and you don’t want to maintain the permissions long term? You have a couple options… You can always create a role and manage the execute permissions to each proc allowing the execute role to have execute permissions.

Or…

You can take advantage of the schema’s in the database. A simple call to a couple system stored procs is going to allow you to grant execute rights to anyone who has permissions to that schema. For Example:

USE YourDB

GO

SP_Addrole
‘YourRole’

GO

EXEC
sp_adduser
‘YourLogin’, ‘YourUser’, ‘YourRole’

GRANT
Execute
ON
SCHEMA
:: DBO TO YourRole

According to Books On Line the stored procedure sp_AddUser is being phased out to be replaced by a Create User Statement.    The Create User Statement looks a lot more like this:

CREATE USER your_database_user FOR LOGIN sqlserver_Login_Account

My only complaint so for with the CREATE USER statement is that I can assign it to a database role as I create it.  There may be a way and I just have not found it yet.


Comments
  1. Mike Walsh says:

    Can’t you also just “GRANT EXECUTE TO” without the schema? Of course either of these options are potentially risky as they tend to violate the idea behind least privilege. I try to shy away from granting blanket read or execute because of a few reasons:

    1.) Lest Privilege is gone. Sure today perhaps there are only procs that truly need to be executed in the DB or Schema… But the moment a new one is added, this user (or hopefully you are assigning the permissions to a role for ease of administration) now has execute on it for better or worse.

    2.) It promotes laziness –> It means you’ve given the development or project team a free pass on identifying exactly what set of permissions are necessary for what type of user and instead are just granting all execute to that user or role.

    This still beats the pants off of “just assign DBO”, though🙂 Good Tip all the same, I just caution using it.

    • Chris Shaw says:

      Mike,

      Good comment. I agree with you that it promotes laziness and violates the theory of least permissions. I guess argument could be made that in the schema all the procs could be organized of just the app or user, but then I think we are splitting hairs. The sad reality that I have seen is most people just do the basics to get by. It is sad… when I am happy when these accounts don’t have DBO. So many databases that I have seen are wide open before I start to lock them down.

      Chris

      • Mike Walsh says:

        That’s a really good point you ended with, Chris. It is pretty pathetic when sometimes you actually get excited that you could just “at least” axe SA or DBO rights that were granted under a “previous administration”. At least we care, though😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s