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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply