Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin fixed role. While this means that the user for all intents and purposes "owns" the SQL Server instance, we have set that instance to a service account specific to that vendor. I'm left with primarily assessing the damage that can be done using xp_cmdshell.

I know that a sysadmin can undo almost anything that is implemented to monitor that activity, but if there is a way to monitor it (using Extended Events perhaps?) and/or log function use it would provide us with an additional level of security.

Audit events are actually not available through Extended Events. You would need to use SQL Server Audit, which more or less works on top of the Extended Events engine (at least from what I understand).

Steps to go with:

• Create a Server Audit

• Create a Server Audit Specification

• Then go back and enable them.

It is fairly straight forward in using the UI via SSMS to create them but for purposes of this post, this would be the T-SQL to do the same thing:

USE [master]
GO

CREATE SERVER AUDIT [cmdshell-20150603-124954]
TO FILE 
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Log\' )
WHERE object_name = 'xp_cmdshell';
GO

CREATE SERVER AUDIT SPECIFICATION [Audit-cmdshell]
FOR SERVER AUDIT [cmdshell-20150603-124954]
ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Then when you go back and view the Audit Collection it you would see something similar to this:

In addition with regard to the audit being turned off you can create a seperate server audit that will specifically log when any SQL Server Audit is disabled or started. This will at most give you who stopped it.

StackExchange Database Administrators Q#103183, answer score: 7