January 19, 2012 at 10:34 am
Hi,
Posted this in the SS2K5 Replication forum a couple of days ago but it seems very quiet there. This issue comes about under SQL 2008 as well so...
We've changed our process for duplicating our live db to our reports db from a simple backup/restore to using snapshot replication. But now when one of our team of developers wants to alter a stored procedure they get the error:
Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
Msg 3609, Level 16, State 2, Procedure sp_MyStoredProc, Line 243
The transaction ended in the trigger. The batch has been aborted.
I don't want to give dbo to the whole team and cannot even find sp_MSreplcheck_publish anywhere (is it auto-generated?)
One suggestion online is to use impersonation, but was hoping somebody on here might be able to make a recommendation.
Any help greatly appreciated.
January 19, 2012 at 12:32 pm
ohack (1/19/2012)
Hi,Posted this in the SS2K5 Replication forum a couple of days ago but it seems very quiet there. This issue comes about under SQL 2008 as well so...
We've changed our process for duplicating our live db to our reports db from a simple backup/restore to using snapshot replication. But now when one of our team of developers wants to alter a stored procedure they get the error:
Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
Msg 3609, Level 16, State 2, Procedure sp_MyStoredProc, Line 243
The transaction ended in the trigger. The batch has been aborted.
I don't want to give dbo to the whole team and cannot even find sp_MSreplcheck_publish anywhere (is it auto-generated?)
One suggestion online is to use impersonation, but was hoping somebody on here might be able to make a recommendation.
Any help greatly appreciated.
Sounds to me like a permissions issue on the publisher set up, not the actual user.
Jared
CE - Microsoft
January 20, 2012 at 7:27 am
Thanks, it is indeed a permissions issue. I've checked and found that the ability to compile stored procedures in the publisher db comes and goes with membership of the dbo role for that db. I'm still left with having to put the whole dev team in there to work around.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply