December 7, 2015 at 1:34 pm
Here's what I'm currently doing, and what I'd like to be able to do:
I have a logon trigger, which based on the number of simultaneous connections a logon has, may rollback a connection attempt. What I would like to have happen is, if / when this rollback occurs, some sort of record of the event.
My first thought was to put an insert statement inside the trigger, but it seems logon trigger cannot return a resultset, and if they do, they will fail and behave just as if the condition the trigger is checking for happened. Obviously this would be a bad result...
My next thought, which sounds like it might work, is to have a RAISERROR happen when the trigger condition is met (similar to this person with an after update trigger: http://www.sqlservercentral.com/Forums/Topic1499938-3077-1.aspx)
My question is, would this fall into the same category as returning a result set?
If so, is there any way to record when my trigger fires, to aid in troubleshooting customer problems?
Thanks,
Jason
December 7, 2015 at 4:59 pm
An INSERT to a table won't try to return a result set ... unless you're missing a:
SET NOCOUNT ON
at the start of the trigger. Verify that statement is present.
Edit: I have logon triggers now that log some things before killing the task, and they run fine.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 5:03 pm
Cool, I'll putz with this on one of my QA servers in the morning!
December 8, 2015 at 5:56 am
Well, that was certainly exciting...
I set up a test trigger that would only block my own non-sysadmin login to test inserting to a table. Pop open another SSMS tab (using my SA account) to monitor the connection counts, and get denied logon...
Turns out, somewhere my new trigger fired against my privileged account!
Thankfully, I still had my tab open with the trigger creation in it open, and was able to go in and disable the test trigger. Even better, a organizationally required server-side trace caught the blocked logon *and* the blocked logon was recorded in the SQL logs!
So now, I can ease back and play with the trigger on my home systems, on my own time, in order to capture data that might be helpful, while knowing I've got the minimum available at work in case someone does have a problem.
Now to figure out why it tried to block me, so I can fix that (Just in case.)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply