Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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
0
tristonyip
Asked:
tristonyip
3 Solutions
 
dbaSQLCommented:
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 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.
0
 
tristonyipAuthor Commented:
can i find out who executed it or who ran the SP ? thanks
0
 
dbaSQLCommented:
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
 
Scott PletcherSenior DBACommented:
SQL does not save which user executed a stored procedure.  It would be way too much overhead.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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