CREATE TABLE PermsTest
(RowID int identity primary key)
GO

CREATE TRIGGER Audit_PermsTest
ON PermsTest
WITH EXECUTE AS OWNER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
	DECLARE @TEMP TABLE
	(EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000))
	INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')
	SELECT EventInfo FROM @TEMP
END
GO

USE [master]
GO

CREATE DATABASE SAPermsTest
GO

CREATE LOGIN [PermsTestLogin] 
WITH PASSWORD=N'c0mpl3xp@$$'
GO

USE [SAPermsTest]
GO 

CREATE USER [PermsTestLogin] FROM LOGIN [PermsTestLogin]
GO

CREATE TABLE PermsTest
(RowID int identity primary key)
GO

GRANT INSERT ON PermsTest TO PermsTestLogin
GO

CREATE TRIGGER Audit_PermsTest
ON PermsTest
WITH EXECUTE AS OWNER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
	DECLARE @TEMP TABLE
	(EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000))
	INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')
	SELECT EventInfo FROM @TEMP
	
	SELECT SYSTEM_USER
	
	SELECT text
	FROM sys.dm_exec_requests 
	CROSS APPLY sys.dm_exec_sql_text(sql_handle)
	WHERE session_id = @@SPID
END
GO

-- Test the trigger will fail.  Make sure to revert
EXECUTE AS LOGIN='PermsTestLogin'
INSERT INTO PermsTest default values;
REVERT
GO

-- Revert Fails when trigger execution fails so do it now.
REVERT
GO

-- Create a certificate to sign stored procedures with
CREATE CERTIFICATE [SAPermsCertificate]
ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
WITH SUBJECT = 'Certificate for signing Audit Triggers';
GO

-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [SAPermsCertificate]
TO FILE = 'D:\SQLBackups\SAPermsCertificate.CER';
GO

-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [SAPermsCertificate]
FROM FILE = 'D:\SQLBackups\SAPermsCertificate.CER';
GO
-- Create a login from the certificate
CREATE LOGIN [SAPermsLogin]
FROM CERTIFICATE [SAPermsCertificate];
GO

-- The Login must have Authenticate Sever to access server scoped system tables
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [SAPermsLogin]
GO

-- Add the VIEW Server State permission to the Certificate login.
GRANT VIEW SERVER STATE TO [SAPermsLogin]
GO

USE [SAPermsTest]
GO

-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::Audit_PermsTest
BY CERTIFICATE [SAPermsCertificate] 
WITH PASSWORD = '$tr0ngp@$$w0rd';
GO

-- Retest the Trigger function and it will work returning 
-- CREATE TRIGGER statement for the sys.dm_exec_sql_text()
EXECUTE AS LOGIN='PermsTestLogin'
INSERT INTO PermsTest default values;
REVERT
GO

-- Cleanup
/*
USE [master]
GO
DROP LOGIN [SAPermsLogin]
DROP CERTIFICATE [SAPermsCertificate]
DROP DATABASE [SAPermsTest]
-- Delete the certificate backup from disk
*/