SQL Server Performance Forum – Threads Archive
Roles
Hi, I am bit perplexed in a situation. Can anybody tell me a way to grant permissions to alter stored procedures by a developer in development env. without giving role of db_ddladmin.? Thanks in advance.RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.
ALTER PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the owner of the procedure, and are not transferable. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks Satya for the quick advise. I am looking at another typical situation, where in I have a request by my developer, where he wants access to few stored procs. of dev. database to alter and not alter any other object. But, as per the given reply by you, it looks he either needs to be db_ddladmin or db_owner (ruled out in our shop) or sysadmin. Is there any solution for this. Pl. help.
RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.
If the developer is the owner of that Procedure then its possible to alter, if not its must and should to go with atleast db_ddladmin (as far as I know). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya, thanks for the reply. now I have a very dumb question, how do make the developer owner of specific stored procedures. I am totally confused and out. Can you pl. help. ? Thanks in advance. RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.
sp_changeobjectowner is the useful SP to change object owner in the database.
Refer to books online for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>