January 22, 2011 at 3:11 am
I have given developers ALTER,EXECUTE,VIEW DEFINITION rights and they were able to execute and ALTER procedures.
However now they are getting following errors when they ALTER PROCEDURE
after I have enabled database for merge replication for few tables.
Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12
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.
I do not wish to provide more rights to developers. Does any one know why above error occurs although I have not replicated any stored procedures. and is there any way so that I do not need to give more rights to developers.
It is MS SQL Server 2008 64 bit running on Windows Server 2008.
January 22, 2011 at 5:36 am
Found the way to get rid of above error. I am posting solution here so that it will help others who are facing same issues. See the script I have created below.
/*Jatin: following trigger is enabled when replication is setup/altered
If it is Enabled then SQL developer will get following error while they try to
Alter any Procedure in database which is in replication.
Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12
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.
Resolution: Disable this database trigger. Since we are not replicating any procedure or functions,
it will not harm if this trigger do not fire.
*/
IF EXISTS (SELECT is_disabled,* FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'
AND name = N'MSmerge_tr_alterschemaonly'
and is_disabled =0 --0 means DB Trigger is ENABLED
)
Begin
DISABLE TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE
End
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply