Error creating a logon trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Error creating a logon trigger

Error creating a logon trigger

Rate Topic

Display Mode

Topic Options

Author

Message

jfrancisco

Posted Wednesday, September 22, 2010 1:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 1:40 PM
Points: 9, Visits: 19

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

Post #991525

Jack Corbett

Posted Wednesday, September 22, 2010 2:02 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 10,613, Visits: 11,952

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #991553

GilaMonster

Posted Wednesday, September 22, 2010 2:10 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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 2008, MVP
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

Post #991557

jfrancisco

Posted Wednesday, September 22, 2010 2:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 1:40 PM
Points: 9, Visits: 19

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

Post #991558

GilaMonster

Posted Wednesday, September 22, 2010 2:15 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #991560

jfrancisco

Posted Wednesday, September 22, 2010 2:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 1:40 PM
Points: 9, Visits: 19

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...

Post #991568

GilaMonster

Posted Wednesday, September 22, 2010 2:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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 2008, MVP
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

Post #991572

Jack Corbett

Posted Wednesday, September 22, 2010 2:33 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 10,613, Visits: 11,952

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.

Post #991574

« Prev Topic | Next Topic »

Permissions