1

I am using replication between two MS SQL Servers (2017 14.0.3421.10). Unfortunately, when replication publishing is switched on, my user for database schema changes is not able to ALTER any columns. (Everything on Publisher side)

CREATE TABLE test (
    id int NOT NULL PRIMARY KEY,
    data1 int NOT NULL,
    data2 varchar(100) NOT NULL
);

CREATE INDEX ON test (data1, data2);

//-- Enable replication

ALTER TABLE test
ALTER COLUMN data2 varchar(101) NOT NULL;

The error looks as follows:

Msg 21050, Level 14, State 1, Procedure sys.sp_MSreplcheck_publish, Line 16 [Batch Start Line 4]
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation

I haven't found any other way till now on how I can make it work without promoting my user to an admin. Thus I tried to understand if there is a minimal permission set I could employ to make this all work.

My initial thought was that my user has no permission to execute sys.sp_MSreplcheck_publish procedure. And unfortunately I don't find this procedure in the master DB, but I found the body of this procedure on the web and the error ID looks like generated by this procedure.

I am confused now. So the function exists and it is really checking if my user is an admin/db_owner. But how then I run scheduled tasks updating schema without promoting the user to an admin?

UPD

Would enabling/disabling publishing for a table work in this case? I assume that the user will still require a bunch of permissions?

This seems to work and if I remove the article from the publication for that particular table, I can run ALTER again. Unfortunately, I still need sysadmin or db_owner to do it in T-SQL via sp_droparticle, even for my own user who is a sysadmin. I receive the same error as above.

6
  • Little confused on your situation. Is the user trying to ALTER the column on the Publisher side or the Subscriber side? What are they trying to change about the column?...providing the SQL code would be helpful. What type of Replication is it?
    – J.D.
    Commented Sep 13, 2023 at 12:28
  • Updated with my test procedure
    – Dex
    Commented Sep 13, 2023 at 12:47
  • And my other questions above? Also, at what point is the test table added to Replication?...after the index is created but before the ALTER statement is ran?
    – J.D.
    Commented Sep 13, 2023 at 12:54
  • Yes, this is a correct sequence.
    – Dex
    Commented Sep 13, 2023 at 12:57
  • So the ALTER statement is happening on the Publisher database? What kind of Replication are you using (Transactional, Merge, Peer to Peer, Snapshot, etc)?
    – J.D.
    Commented Sep 13, 2023 at 14:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.