Getting the username of the user from the program into a audittrail

  • 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.

  • 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".

  • 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.

  • 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

    ;

  • O! I found Host_process_ID to be my computer name! does it reflect all computer names that run a query from the system?

  • 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."

    😎

    BOL: sys.dm_exec_connections & sys.dm_exec_sessions

  • 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!

  • 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