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

database-permissionssql serversql-server-2005stored-procedures

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?

Best Answer

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.

Related Solutions

Sql – Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Sql – How to SELECT FROM stored procedure

You can

create a table variable to hold the result set from the stored proc and then
insert the output of the stored proc into the table variable, and then
use the table variable exactly as you would any other table...

... sql ....

Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params 
Select * from @T Where ...

Best Answer

Related Solutions

Sql – Function vs. Stored Procedure in SQL Server

Sql – How to SELECT FROM stored procedure

Related Topic