October 7, 2010 at 11:10 am
I have transactional replication setup between SQL 2000 and a SQL 2008 instance in a few locations, every thing works perfectly fine, except on one of the developers machines. For some reason on this one machine 2 of the publications fail with the message "'Setuser' permission denied in database db1" 2 other publications work fine. I scripted the publications that failed and set them up on a different box and pushed them to the same subscriber and it works fine, so its something on the distributer. I have gone through this database, and all the right users exist and they all seem to be in the right roles, with the right permissions and everything seems to be setup correctly, except that the Distribution Agent always fails. Distribution Agent runs under the same domain account. I'm trying to avoid reinstalling SQL if I can since other things run on this machine, but I need to get replication working.
October 7, 2010 at 11:28 am
From http://support.microsoft.com/kb/231403 - SETUSER permission is given only to members of the sysadmin role; this permission is not transferable.
I don't know why SETUSER is being invoked but it looks like it is being invoked from an account that does not have sysadmin role. Did you try putting the domain account the distribution agent runs under as a sysadmin (doesn't look like this will help as the same account works from other machines but it's worth a try to see what happens)?
Also - is there any extra information provided if you try to log the distribution agent output to a log file using the -Output switch?
October 7, 2010 at 1:15 pm
I don't know why SETUSER is being invoked but it looks like it is being invoked from an account that does not have sysadmin role. Did you try putting the domain account the distribution agent runs under as a sysadmin (doesn't look like this will help as the same account works from other machines but it's worth a try to see what happens)?
Also - is there any extra information provided if you try to log the distribution agent output to a log file using the -Output switch?
I did make sure that the accounts had the same privileges on both.
and if I run with -output
I get
GO
setuser
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Last transaction timestamp: 0x0006828200000d35002200000000
Transaction seqno: 0x0006828200000d350022
Command Id: 10
Partial: 0
Type: 70
Command: [10/7/2010 3:03:10 PM]JPDOWD.DB1: SET QUOTED_IDENTIFIER ON
exec sp_MSdefer_check @objname = N'BH_FieldMap', @objowner = N'BH1'[10/7/2010 3:03:10 PM]JPDOWD.DB1: drop procedure "BH1"."BH_SEL_PrivateLabelFieldMap"
[10/7/2010 3:03:10 PM]JPDOWD.DB1: CREATE PROCEDURE "BH1"."BH_SEL_PrivateLabelFieldMap"
( @entity varchar(100), @privateLabelID int)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT columnName, display, editType, isRequired, isHidden, allowMultiple,
defaultValue, valueList, displayList, description, hint, limit, sortOrder, isDescending FROM BH_FieldMap
WHERE entity = @entity AND privateLabelID = @privateLabelID AND editType <> 'System' AND editType <> 'Custom Component'
ORDER BY sortOrder, fieldMapID
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END
[10/7/2010 3:03:10 PM]JPDOWD.db1: setuser
Agent message code 20046. SETUSER permission denied in database 'db1'.
[10/7/2010 3:03:10 PM]JPIMS9.distribution: {call sp_MSadd_distribution_history(18, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 7, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 19,
Transaction Seqno = 0006828200000d35002200000000, Command ID = 7
Message: Replication-Replication Distribution Subsystem: agent JPIMS9-db1-BH_MASTER_Field-JPDOWD-18 failed. SETUSER permission denied in database 'db1'.[10/7/2010 3:03:10 PM]JPIMS9.distribution: {call sp_MSadd_repl_alert(3, 18, 19, 14151, ?, 7, N'JPIMS9', N'DB1', N'JPDOWD', N'DB1', ?)}
ErrorId = 19, SourceTypeId = 5
ErrorCode = '262'
ErrorText = 'SETUSER permission denied in database 'db1'.'
[10/7/2010 3:03:10 PM]JPIMS9.distribution: {call sp_MSadd_repl_error(19, 0, 5, ?, N'262', ?)}
Category:SQLSERVER
Source: JPDOWD
Number: 262
Message: SETUSER permission denied in database 'DB1'.
[10/7/2010 3:03:10 PM]JPDOWD.DB1: exec dbo.sp_MSupdatelastsyncinfo N'JPIMS9',N'DB1', N'BH_MASTER_FieldMaps', 0, 6, N'SETUSER permission denied in database ''DB1''.'
Disconnecting from Subscriber 'JPDOWD'
Disconnecting from Distributor 'JPIMS9'
Disconnecting from Distributor History 'JPIMS9'
Thats just the tail of the log, but that setuser at the top is the only place it shows up.
October 7, 2010 at 2:06 pm
Looks like you are replicating stored procedures (if I read the log properly) - do the stored procedures have SETUSER in them? Maybe if you don't replicate the particular procedure "BH1"."BH_SEL_PrivateLabelFieldMap" that seems to be having this issue and then see if it works? (yes - I'm down to wild guesses :blink: )
Also - did you try to add the domain account (i.e. the account that the distribution agent job runs under) into the sysadmin group (on the distributor and on the subscriber) and try? Just to rule out the possibility of this being related to the account belonging to the sysadmin group...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply