How do I grant someone the ability to create a stored procedure?

1

March 2, 2016 by Kenneth Fisher

The question of How to grant the ability to create/alter stored procedures and views came up today on dba.stackexchange.com. It’s actually a question I’ve pondered in the past. There are two major methods to do this.

The Scalpel

If you’ve looked at the permissions available in SQL Server you’ll have noticed CREATE PROCEDURE, CREATE VIEW etc. so this seems like it has an easy answer. The reality is that it’s not quite as easy as it looks. It actually took me several years before I really got what was going on.

If you grant a user these permissions

GRANT CREATE VIEW TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];

The user still won’t be able to create procedures or views. And if you think about it in the right way it makes sense. While the CREATE permissions give us the ability to create objects what they don’t give us is a place to put them. Schemas are containers for most of the objects in databases. So in order change the schema (by putting an object in it) we need to grant ALTER on it. So for the CREATE to work we need to:

GRANT CREATE VIEW TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];
GRANT ALTER ON SCHEMA::[dbo] TO [UserName];

Now UserName will be able to create/alter/drop views and procedures within the dbo schema.

FYI it doesn’t really matter what order you run these in.
 
 

The Sledgehammer

Now another option is to add the user to the db_ddladmin role. Sometimes this is the right way to go. Other times it’s like using a sledgehammer to remove the wing from a fly. db_ddladmin grants the ability to create/alter/drop ANY object in the database in ANY schema. This is usually far more permission than you want to grant, but it’s still useful when that’s what you need. Otherwise only grant the permissions that are actually required.

One thought on “How do I grant someone the ability to create a stored procedure?

  1. […] Kenneth Fisher shows the permissions necessary to create a stored procedure: […]

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: