Auditing Stored Procedure Execution

In the previous blog post, I demonstrated how to use SQL Server Audit feature to keep track of login and logout events. This post shows how to keep track of stored procedure executions. Below script creates the audit:

USE [master]
GO
CREATE SERVER AUDIT [Audit_SP_Exec]
TO FILE
(    FILEPATH = N'D:\SqlAudit'            -- *** Modify the File Path ***
    ,MAXSIZE = 50 MB
    ,MAX_ROLLOVER_FILES = 2000
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
GO

USE [TestDB]        -- *** your application DB ***
GO
CREATE DATABASE AUDIT SPECIFICATION [DbAuditSpec_SP_Exec]
FOR SERVER AUDIT [Audit_SP_Exec]
ADD (EXECUTE ON OBJECT::[dbo].[uspTest] BY [public]),
ADD (EXECUTE ON OBJECT::[dbo].[uspTest2] BY [public])
GO
ALTER DATABASE AUDIT SPECIFICATION [DbAuditSpec_SP_Exec] WITH (STATE = ON)
GO

USE [master]
GO
ALTER SERVER AUDIT [Audit_SP_Exec] WITH (STATE = ON)
GO

Then you can view the audit log by right-click the server Audit in SSMS like below: