Error creating a logon trigger

Question

Error creating a logon trigger

jfrancisco

SSC-Addicted

Points: 423
More actions
September 22, 2010 at 1:34 pm

#227160

Hi
I am trying to create this trigger but I am getting an error... I am working in SQL Server 2005 but really the trigger is going to be created in SQL Server 2000.
create trigger dbo.trigger_login_registers
on database
for logon
as
BEGIN
insert into dbo.login_registers values
(SYSTEM_USER, CURRENT_USER, getdate(), host_name());
END;
The error is the following:
Msg 1084, Level 15, State 1, Procedure trigger_login_registers, Line 3
'logon' is an invalid event type.
I am checking the sintaxis and according to it, All is fine...
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

There aren't logon triggers in Sql Server 2000 so you won't be able to move this to a 2000 server.

The reason you are getting this error is because LOGON is a server level event not a database level event so you need to define the trigger as ON ALL SERVER.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

Earliest version that you can use login triggers is SQL 2005 SP2. SQL 2000 does not have any form of DDL trigger.

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

jfrancisco SSC-Addicted Points: 423 More actions · Answer 3

Ahhhh ok Jack. Thanks

and do you know how Can I check that same information ( Login information ) with a data dictionary view. A view call sessions.... or something like that ?

Thanks in advance

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

For SQL 2000? Pretty much only feasible option is server-side trace.

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

jfrancisco SSC-Addicted Points: 423 More actions · Answer 5

thanks GilaMonster

For all....

My case is the following: There is a database in my enterprise which is used by DBA's, Developers and Client People. We believe that Client People are doing changes to the data without autorizathion but We can not deny theirs entrance to the database. We want to comprobate that they are getting access to the database from other client tools different than the application and because of that I want to register the login activity. The idea that I am thinking is to create a job for inserting data each 2min about all the sessions connected to the database for identifying who is connected diferent from our DBAs, Developers and application Server.

And for that I need a data dictionary view to see the sessions information. Can you help with the name of that view ?

Thanks in advance...

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

sysprocesses

However, having being where you are, a 2 minute poll to that table is not adequate. It does not take 2 min to connect, change data and disconnect. Rather go with a server-side trace if you can, you'll catch every single login that occurs.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

In that case you could use sp_who2 to log to a table in a job.

Or you could do a server side trace as Gail suggested.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question