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

Question

If multiple users are sharing sqladmin login then how do we track which ddl, or dml query was run by which person by session or ip or something?

Rijwan Ansari · Answer

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 ;

Adarsh · Answer

Thanks you

Rajanikant Hawaldar · Answer

Execute sp_who2 'active' on master db ,in the results u can get complete info like session id , login name ,db,query type, etc

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

Answers (3)