a stored procedure last executed and who executed it

tristonyip
tristonyip used Ask the Experts™
on
Hi
 how can find out a stored procedure last executed and who executed it from sql sever 2012, thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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%'
Daniel JonesData Research Analyst
Commented:
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.

Author

Commented:
can i find out who executed it or who ran the SP ? thanks
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SQL does not save which user executed a stored procedure.  It would be way too much overhead.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial