FreeHansje (1/11/2010)
I thought I had found the solution for my problem, but alas...I need to call master.sys.dm_exec_sql_text(to find the last used sql-statement) from a trigger, or sp, in another database, by ordinary users without SA-rights; please don't ask why.
I changed reference in your article from msdb into master, used GRANT VIEW SERVER STATE as authorization, but alas, no succes. For days now I am looking for a way to allow non-SA users to view DMV's. Any1 here can help me out?!
You actually don't need any kind of special permissions to solve your problem. First sys.dm_exec_sql_text isn't going to give you the information that you probably want. If you run that for a session_id in a trigger, it is going to return the CREATE TRIGGER statement of the trigger firing the request. As long as the need to get the SQL Statement that was executed to cause the trigger to fire is self session scoped, you can get it without added permissions using DBCC INPUTBUFFER() like follows:
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
If you want to get information from another session that is executing that would require the additional permissions. As long as it stays self scoped it can be called without GRANT VIEW SERVER STATE.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]