Who deleted the rows?

• Rajib Bahar

  Ten Centuries

  Points: 1007

  More actions

  April 17, 2009 at 3:20 pm

  #980018

  try this

  SELECT

  SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

  ((CASE qs.statement_end_offset

  WHEN -1 THEN DATALENGTH(st.text)

  ELSE qs.statement_end_offset

  END - qs.statement_start_offset)/2) + 1) AS statement_text, dbid, pa.value [User ID],

  SUSER_NAME(cast(pa.value as int)), USER_NAME(cast(pa.value as int))

  FROM sys.dm_exec_query_stats AS qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

  OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

  WHERE pa.attribute like '%user_id%'

  Rajib Bahar
  http://www.rajib-bahar.com
  http://www.twitter.com/rajib2k5
  http://www.youtube.com/icsql

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  April 18, 2009 at 2:33 am

  #980135

  From Books Online:

  sys.dm_exec_plan_attributes

  User_ID

  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.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass