July 29, 2005 at 7:14 am
In SQL server. Can I get any usefull information from a trigger? Let say I set up a trigger to watch a table for changes. Can I get what machine did the change come from? Did the change come from a stored procedure, or was it manually (or Enterprise Manager) changed? The user that made the change in this case is going to be SA, so that does me little good.
-Robert
July 29, 2005 at 8:55 am
Have you tried using the HOST_NAME () function to identify the connecting workstation?
> Did the change come from a stored procedure, or was it manually (or Enterprise Manager) changed?
I don't know any way to do this directly. However, there are a couple of things you might try.
One of them is to use the @@NESTLEVEL function, which tells how many levels deep into procedural modules the logic has traveled. If, e.g., in Query Analyzer you were to directly insert a row into a table with an INSERT trigger on it, @@NESTLEVEL inside the trigger itself would have a value of 1. But! If called from a procedure, it would have a value greater than 1, the value itself dependent on how many levels deep the executing procedure happens to be.
A more troublesome approach -- use at your own risk, please -- would be to try to establish communication between any calling procedures and the triggers using global cursors. You wouldn't actually need to open or read the cursors, just declare them in the calling procedure and check for their existence in the triggers (using CURSOR_STATUS). If the cursor doesn't exist when the trigger tests for it, then it was a direct T-SQL action -- if it does, then the procedure that created the cursor must have been the calling agent.
I can't imagine that the latter approach is a "best practice" of any sort. However, I have used it when operating in duct tape/baling wire mode. I half expect Andy Warren to reach out from my monitor and smack me on the forehead for even suggesting it. 🙂
Having brought up (if not exactly recommended) a non-best-practices approach, it may behoove us nevertheless to note that using 'sa' in application security is not really considered a good idea.
August 1, 2005 at 3:22 am
In the same way that you can use HOST_NAME to get the HOST you can also use APP_NAME() to get the application that initiated the proc
From BOL
This example checks whether the client application that initiated this process is a SQL Query Analyzer session.
DECLARE @CurrentApp varchar(35)SET @CurrentApp = APP_NAME()IF @CurrentApp <> 'MS SQL Query Analyzer'PRINT 'This process was not started by a SQL Query Analyzer query session.'GrahamAugust 1, 2005 at 5:46 pm
There are plenty of good responses. SESSION_USER is one I would add.
We used triggers to add auditing to key tables. We inherited code from several platforms, and added SESSION_USER. Since each platform used a different login, we could nail down how each was changing things.
This only works since we used low-privilege accounts for the apps. Any DBA changes show as "dbo"
Mike
August 2, 2005 at 7:03 am
Another useful source of info that works in triggers is
SELECT * from master.dbo.sysprocesses WHERE spid = @@spid
sysprocesses has lost of stuff: login, host, NT login, appname, (but not the nesting level)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply