October 7, 2016 at 12:29 am
Hi!, I'm getting confused and I would like to ask for help, you see, I have this trigger
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 (suser_sname(), (select employee_name from deleted))
END
GO
which SHOULD save the username and what the user changed from the system to the database. The problem is suser_sname() returns the username used as your sql login, in my case, its the connection string which all of my systems are attached to, the users does not have any unique login so I cant use this method, and so is SYSTEM_USER and CURRENT_USER. However, I created a users list in my system. I would like to get the username from the users list and save it to the database in my trigger, any head up is welcome.
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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 ((SELECT SES.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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply