July 15, 2021 at 8:43 am
Hello,
I want to create a Extended Event that logs all queries from a specific user or every user except a couple users accounts.
can someone help me with that.
July 15, 2021 at 11:50 am
To do this you're going to need to use an Action in the events you capture. Which action really depends on your system setup. You can use username, session_principal_name, nt_user_name, or server_principal_name, whichever is appropriate to your setup and needs. You can add that Action to the Event if you want to collect it as well as filter on it. Conversely, you can just filter on it and not collect it.
Here's an example that uses various actions, including server_principal_name, to filter, without adding the action to the data collected:
CREATE EVENT SESSION WhoChangedWhat
ON SERVER
ADD EVENT sqlserver.object_altered
(SET collect_database_name = (1)
ACTION
(
sqlserver.client_app_name,
sqlserver.server_principal_name,
sqlserver.session_id,
sqlserver.sql_text
)
WHERE (
package0.equal_uint64(ddl_phase, (1))
AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'tempdb')
AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'master')
AND package0.not_equal_uint64(object_type, (21587))
AND sqlserver.server_principal_name <> N'WIN-VP70KPNJE7H\Grant'
)
),
ADD EVENT sqlserver.object_created
(SET collect_database_name = (1)
ACTION
(
sqlserver.client_app_name,
sqlserver.server_principal_name,
sqlserver.session_id,
sqlserver.sql_text
)
WHERE (
package0.equal_uint64(ddl_phase, (1))
AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'tempdb')
AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'master')
AND package0.not_equal_uint64(object_type, (21587))
AND sqlserver.server_principal_name <> N'WIN-VP70KPNJE7H\Grant'
)
),
ADD EVENT sqlserver.object_deleted
(SET collect_database_name = (1)
ACTION
(
sqlserver.client_app_name,
sqlserver.server_principal_name,
sqlserver.session_id,
sqlserver.sql_text
)
WHERE (
package0.equal_uint64(ddl_phase, (1))
AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'tempdb')
AND sqlserver.not_equal_i_sql_unicode_string(database_name, N'master')
AND package0.not_equal_uint64(object_type, (21587))
AND sqlserver.server_principal_name <> N'WIN-VP70KPNJE7H\Grant'
)
)
ADD TARGET package0.ring_buffer
So, in the example, it's everyone except that name specified. You can just add more AND and OR clauses to specify exactly who you want to exclude.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2021 at 12:57 pm
Thanks al lot , I also want to see the updates and Select is that also possible ?
July 15, 2021 at 3:04 pm
If you're capturing queries, usually either rpc_completed or sql_batch_completed, you'll be getting all queries. Then just filter by the appropriate action and values to limit the queries to a particular user. You can, and probably should, also limit by database name or ID, same way. The reason we use the actions is because the login information isn't captured in the events themselves.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply