December 24, 2009 at 3:32 am
Hi there,
In a trigger I wish to use the DMV sys.dm_exec_sql_text to store info in a table during certain updates on a specific table. Basically I wish to know when some1 changes info and when. However, to use this DMV one needs VIEW SERVER STATE permission to use such a view. Ordinary users do not have this permission.
Can some1 think up a construction, where said trigger runs under this permission?
EDIT:
I've found out I could use the EXECUTE AS statement in a trigger, now I am wondering how I can run this trigger in this way. Help pleas?
Greetz,
Hans Brouwer
December 29, 2009 at 10:54 am
FreeHansje (12/24/2009)
Hi there,In a trigger I wish to use the DMV sys.dm_exec_sql_text to store info in a table during certain updates on a specific table. Basically I wish to know when some1 changes info and when. However, to use this DMV one needs VIEW SERVER STATE permission to use such a view. Ordinary users do not have this permission.
Can some1 think up a construction, where said trigger runs under this permission?
EDIT:
I've found out I could use the EXECUTE AS statement in a trigger, now I am wondering how I can run this trigger in this way. Help pleas?
Ummm, *what* trigger?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2009 at 11:06 am
Keep in mind with a trigger you can use the "inserted" and/or "deleted" objects !
You could just store that info in an audit or logging table an work it out later.
Do you really need te sql text info ???
In that case I would suggest to start a sql trace.
Keep in mind, a trigger is always executed in transaction context of the originating query. If the trigger fails, the whole of the transaction fails !!
Keep your transactions as short as possible !!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2010 at 3:42 am
Sorry for the delay.
Basically, the table-trigger is used to call master.sys.dm_exec_requests to find out which sql-statement has been used. This, with other info is inserted into an audit-table after an update occurs. We are only interested in updates, not insert or deletes.
I have found some articles on cross-database permissions, however, I have yet to find an in-dept article on calling views and procs from the master as an ordinary user. Here is where the VIEW SERVER STATE permission is needed. I think I'm getting there, but slowly.
Tnx for answering.
Greetz,
Hans Brouwer
January 20, 2010 at 5:55 am
Problem solved, thank you all.
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply