Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error creating a logon trigger Expand / Collapse
Author
Message
Posted Wednesday, September 22, 2010 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 22, 2010 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 10,342, Visits: 13,351
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
Posted Wednesday, September 22, 2010 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 40,425, Visits: 36,875
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
Posted Wednesday, September 22, 2010 2:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 22, 2010 2:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 40,425, Visits: 36,875
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
Posted Wednesday, September 22, 2010 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 22, 2010 2:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 40,425, Visits: 36,875
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
Posted Wednesday, September 22, 2010 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 10,342, Visits: 13,351
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

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 #991574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse