October 7, 2016 at 11:33 am
Prior to updating the table, put the username in a known place, and the trigger can retrieve it from there and use it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2016 at 6:11 pm
So what I did is, save the username in the database through stored procedure. The stored procedure is called in the program prior to update. but sadly, I think I cant save the username from my system to database because I have no way to present it as data to my trigger, the only way is calling a stored procedure and saving it during the update process. Would there be any other way? or I might have confused myself again.
October 7, 2016 at 11:56 pm
joshua 15769 (10/7/2016)
So what I did is, save the username in the database through stored procedure. The stored procedure is called in the program prior to update. but sadly, I think I cant save the username from my system to database because I have no way to present it as data to my trigger, the only way is calling a stored procedure and saving it during the update process. Would there be any other way? or I might have confused myself again.
Quick thought, create a function that grabs whichever user details you need from the current session/connection and use the function as the default value constraint on a column in the audit table.
😎
Here is an example function that grabs all the columns from sys.dm_exec_connections and sys.dm_exec_sessions, obviously one can skip anything that's not needed.
CREATE FUNCTION dbo.SUDF_GET_SESSION_CONNECTION_INFO()
/*
Returns user, session, execution and relevant system information for auditing and diagnostics/delbuging
*/
RETURNS XML
AS
BEGIN
RETURN
(
SELECT * FROM
(
SELECT
SCON.session_id
,SCON.most_recent_session_id
,SCON.connect_time
,SCON.net_transport
,SCON.protocol_type
,SCON.protocol_version
,SCON.endpoint_id
,SCON.encrypt_option
,SCON.auth_scheme
,SCON.node_affinity
,SCON.num_reads
,SCON.num_writes
,SCON.last_read
,SCON.last_write
,SCON.net_packet_size
,SCON.client_net_address
,SCON.client_tcp_port
,SCON.local_net_address
,SCON.local_tcp_port
,SCON.connection_id
,SCON.parent_connection_id
,SES.login_time
,SES.host_name
,SES.program_name
,SES.host_process_id
,SES.client_version
,SES.client_interface_name
,CONVERT(VARCHAR(100),SES.security_id,1) AS security_id
,SES.login_name
,SES.nt_domain
,SES.nt_user_name
,SES.status
,CONVERT(VARCHAR(100),SES.context_info,1) AS context_info
,SES.cpu_time
,SES.memory_usage
,SES.total_scheduled_time
,SES.total_elapsed_time
,SES.last_request_start_time
,SES.last_request_end_time
,SES.reads
,SES.writes
,SES.logical_reads
,SES.is_user_process
,SES.text_size
,SES.language
,SES.date_format
,SES.date_first
,SES.quoted_identifier
,SES.arithabort
,SES.ansi_null_dflt_on
,SES.ansi_defaults
,SES.ansi_warnings
,SES.ansi_padding
,SES.ansi_nulls
,SES.concat_null_yields_null
,SES.transaction_isolation_level
,SES.lock_timeout
,SES.deadlock_priority
,SES.row_count
,SES.prev_error
,CONVERT(VARCHAR(100),SES.original_security_id,1) AS original_security_id
,SES.original_login_name
,SES.last_successful_logon
,SES.last_unsuccessful_logon
,SES.unsuccessful_logons
,SES.group_id
,SES.database_id
,SES.authenticating_database_id
,SES.open_transaction_count
,CONVERT(VARCHAR(100),SCON.most_recent_sql_handle,1) AS most_recent_sql_handle
FROM sys.dm_exec_connections SCON
OUTER APPLY sys.dm_exec_sessions SES
WHERE SCON.session_id = @@SPID
AND SES.session_id = @@SPID
) AS RWLog
FOR XML AUTO
)
END
;
October 8, 2016 at 12:38 am
O! I found Host_process_ID to be my computer name! does it reflect all computer names that run a query from the system?
October 8, 2016 at 12:49 am
joshua 15769 (10/8/2016)
O! I found Host_process_ID to be my computer name! does it reflect all computer names that run a query from the system?
The value of the sys.dm_exec_sessions.host_process_id is defined in BOL as "Process ID of the client program that initiated the session. The value is NULL for internal sessions. Is nullable."
😎
October 8, 2016 at 2:18 am
So I marked your reply as the answer, I used hostname as the source.
ALTER TRIGGER [dbo].[SaveUsers]
ON [dbo].[Employee]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into dbo.testaudittrail(userid,userchanged)
values ((SELECTSES.host_name FROM sys.dm_exec_connections SCON OUTER APPLY sys.dm_exec_sessions SES WHERE SCON.session_id = @@SPID AND SES.session_id = @@SPID), (select employee_name from deleted))
END
It may not save the usernames but, we have unique computer names, I think that's enough. Thank you!
October 8, 2016 at 2:22 am
You are very welcome.
😎
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply