HELP! Logon trigger

Question

HELP! Logon trigger

Anders Pedersen

SSChampion

Points: 11410
More actions
May 10, 2007 at 9:02 am

#172366

So I um, was experimenting with Logon Triggers on my test server. Now I can't logon, even with SA...
Here is my create statement. Any ideas for how to fix this? And why is it even not allowing SA to get in?
create
trigger Login_Audit
on
all server
for
logon
as
begin
DECLARE @data XML
SET @data = EVENTDATA()
insert into Login_Log (LoginName,LoginType)
values (
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)'),
@data.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(30)')
)

end
-- trigger

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

ok found the reason, failed to put in the database name the table was in. Now, is there any way to bypass this? Even DAC won't work.....

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

Well that was interresting. Either the documentation needs to be better or I missed something.

Restarted the server with -f -m flags (minimal, single user). Then I could log on and delete the trigger.

Now changing the trigger to have the proper path to the table, it still fails. Looks like you do not have authority yet to insert into tables inside the trigger, so need to use Execute As on the trigger. In retrospect kind of makes sense I guess.

Thought I would document this in case anyone else runs into the same thing

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Thanks. I'm planning to put a similar trigger on a couple of my servers. Good to know about the execute as

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Anders Pedersen SSChampion Points: 11410 More actions · Answer 4

I put this into production this weekend, and seems to be working fine so far. Here are the additional steps I took to lower the risk of a potential server outage.

Made a permanent backup of master before putting the trigger in, a copy of which I will keep safe... This is the Just In Case backup.

Tested the scripts to create everything locally on my own box instead of the regular development server. This was a good thing as I locked myself out 6 times before I got it right (right meaning the least amount of permissions needed for the login I use as the Execute As login and debugged in general).

Created a view on the table I log the activity in with SchemaBinding on the table and all columns so that it can't easily be changed to make the trigger INOP.

One thing I tried, but could not get to work, was the DAC connection. For some reason it would not allow me to create one to our test server even though I have DAC enabled. I THINK that the DAC connection would bypass the trigger just like starting it up in minimal configuration and single user mode. If anyone has any feedback on this would like to hear about it.