question

Chandoo5599 avatar image
Chandoo5599 asked

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

sql-server-2005securitypermissions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John Sansom avatar image
John Sansom answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for including the usage of roles.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

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]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.