How to Grant Permission to View Database Audit Logs to Non-Sysadmin Login – SQL Server

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.

DENY trumps GRANT, so yeah, you can’t let people view plans if you’ve explicitly denied them the ability to alter trace. But just because you haven’t denied a right doesn’t mean it has been granted.

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE.

It also shows that, unless you explicitly grant any trace-related permissions, they won't be able to read trace data, or even be able to tell there are traces running at all, unless they have inherited those rights in some other way (Windows group, server role, etc). So you don't need to explicitly deny ALTER TRACE in order to protect trace data from these users - just be aware that it is possible for them to can inherit it without an explicit grant.

USE master;
GO
CREATE LOGIN blob WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blob FROM LOGIN blob;
GO
GRANT SHOWPLAN TO blob;
GO
CREATE TABLE dbo.splunge(plonk INT);
GO
GRANT SELECT ON dbo.splunge TO blob;
GO
EXECUTE AS USER = 'blob';
GO
SET SHOWPLAN_XML ON;
GO
-- plan is shown:
SELECT plonk FROM dbo.splunge;
GO
SET SHOWPLAN_XML OFF;
GO
-- error:
EXEC sp_trace_getdata @traceid = 1;
GO
-- empty result set:
SELECT * FROM sys.traces;
GO
REVERT;
GO
USE master;
GO 
DROP DATABASE floob;
DROP LOGIN blob;