• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

a stored procedure last executed and who executed it

 how can find out a stored procedure last executed and who executed it from sql sever 2012, thanks
3 Solutions
Use the sys.dm_exec_query DMV for details on when it was last executed.

The sys.dm_exec_query DMV returns aggregate performance statistics for cached query plans.  Important note:  It is only cached query plans.  When a plan is removed from the cache (or you restart the SQL Server service), the corresponding rows are eliminated from this reference.

           qt.[text] [ProcedureName],
           qs.last_execution_time [LastRan],
           qs.execution_count [ExecutionCount]  
           sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
           qt.text LIKE '%your procedure name%'
Daniel JonesData Research AnalystCommented:
select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id('DBNAME')) = @proc_nm 

Open in new window

It will tell you the last time a stored proc was executed.

As far as I know who ran it is not recorded. You could use profiler to recored this.
tristonyipAuthor Commented:
can i find out who executed it or who ran the SP ? thanks
Retroactively, I am not too sure if you can identify who executed a procedure. Proactively, however, I believe you can use a trace or even an audit.
Scott PletcherSenior DBACommented:
SQL does not save which user executed a stored procedure.  It would be way too much overhead.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now