SQL Server 2005 - Granting permission to create stored procedures (but no other objects)

Question

I want to grant a user permission to create, alter, and execute stored procedures, but without the ability to create other database objects. I excluded them from the role db_ddladmin, but explicitly granted the permissions "Create procedure" and "Execute". These permssions appear in the effective permissions list. However, when I try to create a stored procedure with this login, I get the following error:

"The specified schema name "dbo" either does not exist or you do not have permission to use it."

Any suggestions?

Answer 1

If you can create (or alter) and execute a stored procedure, you can do anything.

Execute on a stored procedure implies all other permissions within the context of that procedure. So if you can change a procedure and run it, there's no point in restricting anything else. You could just make the procedure act as a proxy on your behalf.

Answer 2

A user can create procedures in a schema that they own. So you can set up a schema for the user to do development work. Then, if it needs to be dbo, the admin can put it there when development is done.