I am running a number of servers in a WAN, all running SQL Server 2000 and having merge replication. After upgrading to Service pack 3, all hell broke loose. All of a sudden users cannot save data unless they are dbos! I have ran a number of traces and what appears to be happening is that:
On save, a system stored procedure sp_check_for_sync_trigger fires, and if you are not a member of dbo or sysadmin role, you are denied saving.
Have you come accross this problem elsewhere and if so, what solution besides possibly modifying the sp_check_for_sync_trigger on the publisher so that we remove the additional security check lines in the stored procedure so that it no longer checks for db_owner and sysadmin roles?
I really don't understand. That procedure doesn't work that way. There are several checks this procedure does:
- Check to ensure the procedure is being called from the specified trigger
- Check to ensure the caller of the proc is a predefined replication trigger
- Ensure it is not a nested trigger
I've applied SP3 to several systems and haven't encountered anything related to this. This is also why services packs and hotfixes should never be applied directly to a production environment and should be tested on a similarly configured system prior to deployment. The only recommendation I have is to open a support case with Microsoft to diagnose and fix the problem you are having.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.