For those interested, you can use the same method to give SA rights or access like VIEW SERVER STATE to users with no access. For example to call sys.dm_exec_sql_text() as originally requested:

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

*/

If you run the above, you will note that as I stated in my last response the sys.dm_exec_sql_text() DMF will not output the last statement called by the client it will output the last statement executed by the session which happens to be the execution of the trigger itself. Kind of self defeating code, at least in my opinion which is why I still fall back on good ole' trusty DBCC INPUTBUFFER to get the last input statement from the client.