x

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

avatar image

Chandoo5599
118 6 7 9

(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

avatar image

John Sansom
897 2 4

  • 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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

TimothyAWiseman
15.6k 22 51 38

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x2017
x200
x110

asked: Mar 23, 2010 at 03:33 AM

Seen: 1640 times

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

Copyright 2016 Redgate Software. Privacy Policy