Granting permissions to a user on existing and future stored procedures

I want to grant permissions to a user to create,alter,execute on existing sp's or that will be created in future in dev env for SQL Server 2005

more ▼

asked Mar 23, 2010 at 03:33 AM in Default

Chandoo5599 gravatar image

118 6 7 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I agree with Matt and in addition I would also suggest that you consider taking it a step further by creating a Database Role to manage this security implementation. Assigning a DB Role with EXECUTE to the Schema would mean that new users added to the database that also required the same privileges could simply be added to the role.

Doing so provides a clear and scalable security architecture.

more ▼

answered Mar 23, 2010 at 04:55 AM

John Sansom gravatar image

John Sansom
897 2

+1 for including the usage of roles.
Mar 23, 2010 at 12:20 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

You'll need to grant execute to the user on the schema, this way they'll have the permission at the schema level. If you have multiple schemas, you'll need multiple grants...

GRANT EXECUTE TO [User] ON [Schema] 
more ▼

answered Mar 23, 2010 at 04:25 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

If you want to control the sps by schema, then I think Matt has the answer. If you want them to be able to execute all stored procedures for that database, simply:

Grant Execute to [user]

This of course gives them considerable authority and I would generally not use it in production, but it may be what you want in a development environment.

In fact, in a development environment I routinely add developers to the database owner role, it makes things simpler and while it assumes some risk in a development environment it can be a good trade off to make.

more ▼

answered Mar 23, 2010 at 12:58 PM

TimothyAWiseman gravatar image

15.6k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 23, 2010 at 03:33 AM

Seen: 1451 times

Last Updated: Mar 23, 2010 at 06:32 AM