May 9, 2012 at 3:55 pm
I have a trigger that will write out an audit trail record with the changed values to an audit file. It works fine. I would also like to log in the audit trail the username that was logged on to the application when the change was made.
This is an asp.net application using VS2010 and sql server 2008 (developing on sqlexpress) and I keep the username in a session variable. Can anybody suggest how I might pass the username from the code-behind file to the sql trigger. I thought about incorporating a field in each file called "username" so that it would actually be part of the record being updated, which would make it available to the sql trigger code. While that would probably work, it seems very kludgy and I'm hoping there is a better way.
May 9, 2012 at 5:45 pm
IF you are not using the Workstation ID / host_name for anything, that is quite handy...
Just set the Workstation ID in your connection string (a SqlConnectionStringBuilder helps nicely for this)
e.g.
Data Source=myDBServer;Initial Catalog=ssc;Integrated Security=True;Workstation ID=MyAppUser
Then in your triggers / stored procs you can use the host_name() function to pull the application user from the connection.
An alternative is to open your SQL connection, then set the context_info to the username:
DECLARE @user VARBINARY(128)
SET @user = CONVERT(VARBINARY(128),'MyAppUser')
SET CONTEXT_INFO @user
Then reference it by using the context_info() function in your code...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 10, 2012 at 12:28 am
Dear mister.magoo
As per my understanding
The hostname returns the name of the machine that the connection was established .
The hostprocess is the PID (process id) on the machine that the connection was established from.
Workstation Id gets a string that identifies the database client.
All these are client machine info and we could not set a user defined values to these property.
The user wants to get the username that stored in session . So how can we assign the username into workstation Id ?.
May 10, 2012 at 2:50 am
Ok, so if that doesn't appeal, just set CONTEXT_INFO after you open the connection
DECLARE @ci VARBINARY(128)
SET @ci = 'MyUser'
SET CONTEXT_INFO @ci
Then in your trigger,
use CONVERT(VARCHAR(128),CONTEXT_INFO()) as the user name.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 10, 2012 at 4:08 am
Otherwise, use ModifiedBy Column in the parent table and refer it in the trigger.
May 10, 2012 at 1:17 pm
SYSTEM_USER is available in a trigger, just enable kereberos delegation on the application pool running under IIS so that the IIS server authenticates the web user to the sQL server for running the code and the trigger has access to it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply