This article assumes you already have a basic understanding of SQL Server Audit, but if not, use this link to catch up on all the details.

Are you required to have xp_cmdshell enabled on one of your servers? If so, then setup a SQL Audit now to track its use. Never mind the implications of enabling xp_cmdshell, as a DBA you are responsible for what happens on your servers and tracking the use of xp_cmdshell should be a priority.

The first step is to create a server audit to hold the events that we collect.

USE master;
GO
CREATE SERVER AUDIT Audit_xp_cmdshell
TO FILE (FILEPATH = 'E:\SQL2017\SQL_Audit')
WITH (ON_FAILURE = CONTINUE)
WHERE (object_name = 'xp_cmdshell');
GO

You’ll notice that we added a WHERE clause that instructs the audit to only collect events that reference the object xp_cmdshell. All other events will be ignored.

Next, we need to create a server audit specification using the
SCHEMA_OBJECT_ACCESS_GROUP. This server-level action group is triggered when a permission is used to access an object such as xp_cmdshell.

CREATE SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell
FOR SERVER AUDIT Audit_xp_cmdshell
  ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Running the following commands will make sure both the audit and audit specification are enabled.

ALTER SERVER AUDIT Audit_xp_cmdshell WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell WITH (STATE = ON);
GO

To test our audit, we need to make sure xp_cmdshell is enabled.

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE;
GO

Then call xp_cmdshell to create some activity.

EXEC xp_cmdshell 'DIR E:\SQL2017\SQL_Tempdb\*.* /b';
GO

Viewing the audit log, you can clearly see the command that was executed and the login that called it.

As we have seen, if you have a server that has xp_cmdshell enabled, then using SQL Audit can help you keep track of the commands that have been executed.

Everything we have covered here will work in all editions of SQL Server 2012 and above.