If multiple users are sharing sqladmin login then how do we track whic

You can track the execution with client details as shown

Create Execution table.

Insert query

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
You can track the execution with client details as shown
Create Execution table.
CREATE TABLE [ExecutionLog] (
        [ExecutionId]            NOT NULL,
        [ExecutionName]         VARCHAR(512)  NOT NULL,
        [SQLLogin]              VARCHAR(512)  NOT NULL,
        [ClientNetAddress]      VARCHAR(128)  NOT NULL,
        [ClientHostName]        VARCHAR(128)  NOT NULL,
        [ClientProgramName]     VARCHAR(256)  NOT NULL,
        [StartTime]             DATETIME2     NOT NULL,
        [EndTime]               DATETIME2     NULL,
        [ExecutionParams]       VARCHAR(MAX)  NULL,
        [Outcome]               VARCHAR(64)   NULL,
        [ErrorDetails]          VARCHAR(MAX)  NULL
)
Insert query
INSERT into [ExecutionLog] (
    ExecutionName,
    SQLLogin,
    ClientNetAddress,
    ClientHostName,
    ClientProgramName,
    StartTime
)
SELECT
    @ExecutionName,
    s.login_name,
    c.client_net_address,
    s.[host_name],
    s.program_name,
    ISNULL(@StartTime,SYSDATETIME())
from sys.dm_exec_sessions s
left join sys.dm_exec_connections c
on s.session_id = c.session_id
WHERE s.session_id = @@spid
;
+1
Adarsh5y ago
Thanks you
0
Rajanikant Hawaldar5y ago
Execute sp_who2 'active' on master db ,in the results u can get complete info like session id , login name ,db,query type, etc
0