Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a user which can read and write but now he needs to be able to create/alter a sproc but giving him ddl_admin will make it too unsecure. I dont trust him that much.

What can I do about it? Can I somehow give him rights to alter a sproc in only one schema (I will create a schema for his own use)?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

SQL Server 2005+ have far more granular permissions

GRANT CREATE PROCEDURE TO MyRoleorUser
GRANT ALTER ON SCHEMA:TheSchemaName TO MyRoleorUser

"ddl_admin" is simply a legacy wrapper for these new permissions

From CREATE PROCEDURE in MSDN

Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

Edit:

If your schema is only for stored procs then you should be OK, with no side effects such as the ability to change permissions or createtables

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.