Problem Altering Replicated sp's | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem Altering Replicated sp’s

Hello I have a Development server which is the distributor for a transactional publication, though it seems that you have to be in the db_owner or sysadmin group to alter any of the replicated stored procedures, i dont want to add all the developers to the db_owner role and i wanted to know if there is some way around this? the dev server is running windows 2003, SQL Server 2005 with service pack 1. Thank you.
Replicated stored procedures are no different to usual stored procedures, if the user has DB_DDLADMIN permission then they should be able to modify the SPs. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
??
the developers group have been added to the db_ddladmin, db_executor (database wide execution rights) the db_writer and db_reader groups and yet they are unable to modify their sp’s. if I explicitly grant them the right to alter a sp they are still unable to do so unless they are part of the db_owner group. If I remove the replication (without modifying any permissions) they are able to alter the sp’s. I receive the following error message when I created a test account that tried to modify a replicated SP without being part of the db_owner group: Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16 Only members of the sysadmin or db_owner roles can perform this operation. Msg 3609, Level 16, State 2, Procedure date, Line 5 The transaction ended in the trigger. The batch has been aborted. It seems to check if the SP is being replicated, and if so revokes the right to modify it unless you are a sysadmin or db_owner. In production it is not a problem (as generally only the DBA’s do any rollouts), but in the development environment I need the developers to be able to work (it must be in a replicated environment as the database in question needs to go to several places and must reflect the production environment) but I do not want to grant them db_owner privileges, Especially in SQL 2005 where (I might be mistaken) db_owners can drop the database.
]]>