Allow non-db_owner INSERT permissions in merge replication with mgmt auto-identity range

I have users with INSERT permissions for a table. They can insert records into the publisher until the main range of the table ends. Then they start getting this error every time they try to do an INSERT:

[Microsoft] [SQL Server ODBC driver] Fractional truncation
[Microsoft] [SQL Server ODBC driver] [SQL Server] Insert failed. It contradicted the limitations of verifying the identity range in the TaxDB database, the replicated table dbo.ClientHistory, and the ClientHistoryID column. If the identity column is automatically controlled by replication, update the range as follows: for the publisher, execute sp_adjustpublisheridentityange; for the Subscriber, start the distribution agent or the merge agent.
[Microsoft] [SQL Server ODBC driver] [SQL Server] Statement completed. ODBC - insert into the linked table "ClientHistory" failed.

According to MS Documentation for SQL Server 2008 R2:

If the publisher exhausts its identification range after insertion, it can automatically assign a new range if the insertion was performed by a member of the db_owner fixed database function. If the insertion was performed by a user who is not a member of this role, the log reader agent, the merge agent, or the user who is a member of the db_owner role must run sp_adjustpublisheridentityange (Transact-SQL) .

So the docs say that the user should be a member of the db_owner role, but they don’t say why. Here is the applicable T-SQL section from one of the MSmerge_ins auto-generated triggers:

if is_member('db_owner') = 1
begin
    -- select the range values from the MSmerge_identity_range table
    -- this can be hardcoded if performance is a problem
    declare @range_begin numeric(38,0)
    declare @range_end numeric(38,0)
    declare @next_range_begin numeric(38,0)
    declare @next_range_end numeric(38,0)

    select @range_begin = range_begin,
           @range_end = range_end,
           @next_range_begin = next_range_begin,
           @next_range_end = next_range_end
        from dbo.MSmerge_identity_range where artid='A2D114CE-8436-48BF-9235-E47A059ACB13' and subid='2689FFDE-991E-4122-BFC2-C9739CC55917' and is_pub_range=0

    if @range_begin is not null and @range_end is not NULL and @next_range_begin is not null and @next_range_end is not NULL
    begin
        if IDENT_CURRENT('[dbo].[ClientHistory]') = @range_end
        begin
            DBCC CHECKIDENT ('[dbo].[ClientHistory]', RESEED, @next_range_begin) with no_infomsgs
        end
        else if IDENT_CURRENT('[dbo].[ClientHistory]') >= @next_range_end
        begin
            exec sys.sp_MSrefresh_publisher_idrange '[dbo].[ClientHistory]', '2689FFDE-991E-4122-BFC2-C9739CC55917', 'A2D114CE-8436-48BF-9235-E47A059ACB13', 2, 1
            if @@error<>0 or @retcode<>0
                goto FAILURE
        end
    end
end 

, INSERT ( ) . db_owner . , , . , .

mgmt SQL Server 2008, , " " . , . , , , .

0

All Articles