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
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, , " " . , . , , , .