1

Is there a way to obtain user who ran a query if I have the sql_handle, query_hash, plan_handle, query_plan_hash? Could I use any of these data points to find out the user who ran the query?

Leo Torres
  • 134
  • 4

1 Answers1

4

No. Query plans are shared across sessions, and by default there's no historical record of which sessions ran which queries.

You can capture this data with Audit or Extended Events.

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
  • Hi, David! What's about user_id from sys.dm_exec_plan_attributes in case the value is not -2? – Denis Rubashkin Aug 10 '21 at 14:48
  • "Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database." https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-plan-attributes-transact-sql?view=sql-server-ver15 IE if the object names in the query are resolved relative to a user's default schema it may not be reusable by other users. – David Browne - Microsoft Aug 10 '21 at 14:50
  • So, in case somewhere in the statement a table was called without its schema name we theoretically could get who ran that statement by the user_id? – Denis Rubashkin Aug 10 '21 at 14:57
  • Sometimes. But in the normal case where the user's default schema is dbo and the objects are in the dbo schema, user_id in that DMV will be 1, the user_id of dbo. As the plan can be reused by any user whose default_schema is dbo. – David Browne - Microsoft Aug 10 '21 at 15:11
  • Thanks for the explanation, David! I have checked it out and now could confidently say that in fact the field named user_id contains schema_id of the default schema of a user who ran the query. – Denis Rubashkin Aug 10 '21 at 15:59
  • Yes. Good catch. It's the schema_id from sys.schemas, not the principal_id from sys.database_principals. – David Browne - Microsoft Aug 10 '21 at 16:05
  • If we can match on 1 which is default dbo. I am ok with that 97% of our tables are dbo any way so that is better than 0. If this is possible how would you join the two tables? – Leo Torres Aug 10 '21 at 20:52
  • It would join to sys.database_principals on default_schema_name. So it wouldn't you could not tell which of the users with default_schema of dbo ran the query. – David Browne - Microsoft Aug 10 '21 at 21:06