Queries run by a particular login or Database user in SQL Server 2016

Question

Queries run by a particular login or Database user in SQL Server 2016

MillionQueries

Old Hand

Points: 369
More actions
May 21, 2020 at 5:21 pm

#3756655

Hello,
Is there a way in SQL Server to find out which queries were run by particular login in SQL Server and on which databases?
If somebody can provide a script, that will be great. Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

without already having added something specifically to capture that, like an old-style trace or the current incarnation of capturing via extended events, then no, it is not possible to find anything that was run in the past.

do you have a monitoring application, like Idera or SolarWinds or anything? that kind of monitoring might have some history you can review.

the default trace would have a very short term memory for objects created or dropped, but it is limited in size to 100 meg, and rolls over very quickly.

you would have to add something to capture that kind of information for future review .

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

going forward, you could add something like this extended event as an example.

assume you want to track what [mydomain\IzaguirreL] is querying.

you don't need to identify the domain, as that is a separate column. sql logins you have to track witha differnet columns, so you might need to read up a bit on EE:

the below is capturing three different events to a ring buffer, so i don't run out of disk space:

sqlserver.rpc_completed,sqlserver.sql_batch_completed and sqlserver.sql_statement_completed

CREATE EVENT SESSION [SpecificUserActivity] ON SERVER 
ADD EVENT sqlserver.attention(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[session_nt_user]=N'IzaguirreL'))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[session_nt_user]=N'IzaguirreL'))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[session_nt_user]=N'IzaguirreL'))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[session_nt_user]=N'IzaguirreL'))
ADD TARGET package0.ring_buffer(SET max_events_limit=(2000))
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

This reply was modified 4 years, 7 months ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MillionQueries Old Hand Points: 369 More actions · Answer 3

Thank you very much for your reply Lowell. I appreciate it.

Thank you again.