a stored procedure last executed and who executed it

Hi
 how can find out a stored procedure last executed and who executed it from sql sever 2012, thanks
tristonyipAsked:
Who is Participating?
 
dbaSQLConnect With a Mentor Commented:
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.
0
 
dbaSQLConnect With a Mentor Commented:
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.

     SELECT
           qt.[text] [ProcedureName],
           qs.last_execution_time [LastRan],
           qs.execution_count [ExecutionCount]  
     FROM
           sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     WHERE
           qt.text LIKE '%your procedure name%'
0
 
Daniel JonesConnect With a Mentor Data 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.
0
 
tristonyipAuthor Commented:
can i find out who executed it or who ran the SP ? thanks
0
 
Scott PletcherSenior DBACommented:
SQL does not save which user executed a stored procedure.  It would be way too much overhead.
0
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.

All Courses

From novice to tech pro — start learning today.