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.
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?test
table added to Replication?...after the index is created but before theALTER
statement is ran?ALTER
statement is happening on the Publisher database? What kind of Replication are you using (Transactional, Merge, Peer to Peer, Snapshot, etc)?