SQL Server could not set the Publisher logon name for updatable subscriptions sp_link_publication

Original: SQL Server could not set the Publisher login for updatable subscriptions sp_link_publication

No:

Create an updatable subscription, create the publication normally, create a subscription at the Subscriber, and the last prompt completes with a warning:

Unable to set the publisher login for updatable subscriptions. You may need to use sp_link_publication to set it directly on the subscriber computer.

Suddenly remembered that this publication and subscription database is restored by other libraries, it should be an account problem:

Both the publication database and the subscription database are executed:

--View Orphaned database user exec sp_change_users_login @Action = ' report ';

The database user (Repllinkproxy) that was prompted in the discovery warning is orphaned, that is, a login account that is not associated to the local (repllinkproxy)

--database user associated to SQL Server login exec sp_change_users_login       @action = ' Update_One ',       @usernamepattern = ' Repllinkproxy ' ,    @loginname = ' repllinkproxy '; go  

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

There is another way:

In the final step of creating a subscription, use the existing connection server name as a connection, or you may have the above error prompt!

The workaround is to set the connection security information at the Subscriber by executing the following script:

exec sp_link_publication   @publisher = ' publisher ', @publisher_db = ' publisher_db ', @publication = ' publication ', @s Ecurity_mode = Security_mode

If the following error occurs:

The OLE DB provider "SQLNCLI10" of the linked server "DB" returned the message "The transaction manager has disabled its support for remote/network transactions." "。
Msg 7391, Level 16, State 2, line 1th
The operation could not be performed because the OLE DB provider "SQLNCLI10" of the linked server "DB" could not start the distributed transaction.

The workaround is to right-click the server link, and in the server option, change the "Enable upgrade for RPC for distributed transactions" to "false"and then re-execute the above statement. or SQL Script execution

Use [master]goexec master.dbo.sp_serveroption @server =n ' linkservername ', @optname =n ' remote proc transaction Promotion ', @optvalue =n ' False ' GO

SQL Server could not set the Publisher logon name for updatable subscriptions sp_link_publication