SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error creating a logon trigger


Error creating a logon trigger

Author
Message
jfrancisco
jfrancisco
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 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
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88931 Visits: 14997
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)

Group: General Forum Members
Points: 470320 Visits: 47372
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
jfrancisco
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)

Group: General Forum Members
Points: 470320 Visits: 47372
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
jfrancisco
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)

Group: General Forum Members
Points: 470320 Visits: 47372
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
  Jack Corbett
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88931 Visits: 14997
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search