Sql-server – SQL 2008. How to let user to create/alter sprocs in only one particular schema

database-administrationpermissionssql serversql-server-2008

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)?

Best Answer

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

Related Topic