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

Question

I want to give the user permission to create, modify, and execute stored procedures, but without the ability to create other database objects. I excluded them from the db_ddladmin role, but explicitly granted the Create Procedure and Run permissions. These permssions appear in the list of effective permissions. However, when I try to create a stored procedure with this input, 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

The user can create procedures in his own scheme. In this way, you can customize the design so that the user does the development work. Then, if it should be a dbo, the administrator can put it there when development is done.

Answer 2

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

Execution in a stored procedure implies all other permissions in the context of this procedure. Therefore, if you can change the procedure and start it, it makes no sense to limit anything else. You can simply force the procedure to act as a proxy server on your behalf.