Permissions on procedures

[Nakis]

There is a local user who's been granted the following database roles:
- public
- db_datareader
- db_datawriter
I need to grant the user permission to create and alter non system stored procedures.

How can I do that?

I've read that you can define the user as db_owner, or db_ddladmin.
But if I don't want to do this, is there an alternative?

Some existing procedures have been created by dbo. If this is a problem I can change their owner to the particular user (he was the one requested to create them anyway)

I'd appreciate your reply.
Thank you

 

[Catadmin]

Nakis,

Found this on Microsoft's website:

Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.

Unfortunately, I can't find any details on how to Transfer the "Create Procedure" permissions. Only how to grant other users Execute permissions.

Sorry.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

This should do the trick.

USE DataBase
GO
GRANT CREATE PROCEDURE TO UserName
GO

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com