Retrieve User name from subscriber

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Retrieve User name from subscriber

Author

Topic

rhiel_x
Starting Member

2 Posts

Posted - 2007-11-29 : 19:15:39

I have problem like this.

I have 2 server with merge replication
the first as distributor and the second as subscriber.

I'll try to make trigger from database replication on distributor.
the problem is if any change data on subscriber, i need the user name from subscriber.

right now, i used user_sname to get user name.
but on distributor always get name Administrator.
it's because when subscriber update or delete data, distributor always change/delete/add data from subscriber as Administrator.

but, we know data change from subscriber it's from user. I need to retrieve user name that who change the data.

someone have any solution.

This is my triggers

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tr_insert_jour] ON [dbo].[Jour]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @date1 datetime, @person varchar(30) , @journal_code char(4), @journal_no char(8)
set @date1 = getdate()
set @person = user_name() ----this is user name
select @journal_code = JRN_CODE from inserted
select @journal_no = JRN_NO from inserted
insert into log_insert_Jour values (@date1,@person,@Journal_code,@journal_no)
END

Thanks

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-29 : 19:33:37

You can't do this on the subscriber unless you modify the replication stored procedures (on the publisher) to also pass that information to you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources