Q
Problem solve Get help with specific problems with your technologies, process and projects.

After SP3 install, only dbos can save data

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:

  1. Check to ensure the procedure is being called from the specified trigger
  2. Check to ensure the caller of the proc is a predefined replication trigger
  3. Ensure it is not a nested trigger
There is nothing in this procedure that ties it to a specific user or role or which would prevent replication from continuing unless you were trying to call this directly out of the correct context.

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

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close