-1

I have several users account created in my database which are used by several users. Now I want a way so that I can log all the queries run & action done by those specific users should gets logged.

Is it possible for some specific users to log their events ?

Thanks

Harshit
  • 5,147
  • 9
  • 46
  • 93

2 Answers2

2

From SQL Server 2008 R2 you can use Extends Events under Management->Sessions.

You can create a session logging for sql_statement_completed event and filter for specific user.

You can see more on MSDN and my previous answer for similar issue.

Max
  • 6,821
  • 3
  • 43
  • 59
0

No you cannot do the that i.e, you cannot enable logging for a specific user and leave the rest. You can check the activity from the Activity Monitor using the SQL Server Management Studio if you want to get the specific details of the user.

You can try this query to get the queries:

SELECT cr.DatabaseName
    ,s.session_id
    ,s.host_name
    ,s.program_name
    ,s.client_interface_name
    ,s.login_name
    ,s.login_time
    ,s.nt_domain
    ,s.nt_user_name
    ,c.client_net_address
    ,c.local_net_address
    ,cr.ObjName
    ,cr.Query
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
    ) cr
where s.nt_user_name = '' -- filter here your user name
and s.session_id <> @@SPID
ORDER BY c.session_id

On a side note:

I usually use this query to get the list of queries executed in the past time

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • OK, is there any way or any query from which we can get the list of queries executed by specific user ? – Harshit Oct 05 '15 at 08:06
  • Thanks for your reply. then what could you suggest for getting the logs of the specific users work done on the database, and also, does activity monitor takes more memory, since it will be used on server. – Harshit Oct 05 '15 at 08:15