Does the title of this post trigger your mind? If you all remember, a few days back I had written an article on my interesting observation regarding logon triggers. I would advise you to first read SQL SERVER – Interesting Observation of Logon Trigger On All Servers before continuing with this article further to have a complete idea of the subject.
The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same.
The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on.
Let us verify the above answer.
First, run the following script to create database and logon Audit table.
/* Create Audit Database */
CREATEÂ DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATEÂ TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATEÂ TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERTÂ INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
After that, disable all the other SQL Server Services as delineated in the image below using SQL Server Configuration Manager.
Next, try to logon to the system only one more time.
Check the audit table again to verify if there is a single entry for single login.
I would once again like to thank all of you for active participation and coming up with wonderful suggestions and answers. Let me have your opinion on this observation.
Reference : Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
thanks for answer, but why intellisense ? it has nothing in common with logon trigger, or maybe I`m wrong…
Hi Pinal!
Actually when I create logon triger I normally use evendata() method of Sql Server is that right or not.
Hi All ,
I want to create a trigger that execute every day without any evet like without any DML command can i make this type of Trigger. i search from internet but i cannot get the answer. waiting for reply.
Thankx
hi all,
i need to create trigger that execute when update or add data.that trigger must separate the data to different type of age for example. and make that data to text document.how to do it…please help me…
thanks a lots
I have table 2 tables and i have primary key on both the tablea and Foregn key is primay key of others.
Now how to insert a data in table1.
Script as Follws:
Table 1 scrpt:
CREATE TABLE [dbo].[TEST](
[ID] [int] NOT NULL,
[ID1] [int] NOT NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [TEST]
GO
ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD CONSTRAINT [FK_TEST1_TEST11] FOREIGN KEY([ID1])
REFERENCES [dbo].[TEST1] ([ID2])
GO
ALTER TABLE [dbo].[TEST] CHECK CONSTRAINT [FK_TEST1_TEST11]
========================================
Table2 script
===============================
CREATE TABLE [dbo].[TEST1](
[ID2] [int] NOT NULL,
[ID3] [int] NOT NULL,
CONSTRAINT [PK_TEST1] PRIMARY KEY CLUSTERED
(
[ID2] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [TEST]
GO
ALTER TABLE [dbo].[TEST1] WITH NOCHECK ADD CONSTRAINT [FK_TEST1_TEST] FOREIGN KEY([ID3])
REFERENCES [dbo].[TEST] ([ID])
GO
ALTER TABLE [dbo].[TEST1] CHECK CONSTRAINT [FK_TEST1_TEST]
====================
Dear Pinal
can u please explain me the how o insert data in these tables/
Thanks in advance
Hello Prasad,
you have bounded these two tables in such a way that there is no point to insert the first records.
To insert records in these table at least one condition must be fulfilled:
1. Allow null in foreigk key column in any one table
2. Alter the foreign key constraint to NOCHECK in any one table
3. At least one record exists in any one table.
Kind Regards,
Pinal Dave
Dear Pinal,
Thanks for your sugeestion and it works fine.
Thanks
Prasad
Hi Pinal,
I am using SQL Server 2005 and trying with the trigger query but when ever I am trying to execute the trigger statement it states “LOGON’ is an invalid event type.
Hmm. I have a problem with Reporting Services and reports (WWW). After using your solution WWW RS doesn’t work…
Just another way to prove the fact that logon trigger can fire up multiple times:
Earlier in the year, I explored how SSMS has the potential to open up multiple connections to the SQL Server –
Because there are multiple connections being established, multiple logins have to take place and therefore the login trigger has to be fired multiple times (once per connection).
Hi,
you never trap login failed messages
hi, i got problem. I was create trigger logon, but the reference of database don’t exists in trigger. So, now trigger generate error at the login in SQL and i can’t login at SQL Server 2008 R. How can i do, for login and disable trigger logon.
Thank you
hello
i have fired the above query and then close the sql server . But the problem arises when i tried again to logon that server, this gives me an error “LOGIN FAILED DUE TO THE EXECUTION OF TRIGGER”. please give me any suggestion how can i login on that server .
Hi Rashmi,
To disable that trigger, Open cmd prompt and enter
“Sqlcmd –S localhost –d master –A” and replace localhost with your instancename. and enter
1> enter “DISABLE TRIGGER Trigger_name ON ALL SERVER ” Replace with your trigger name and enter
2> GO and enter.
Now you should be able to login in to your SSMS.
Cheers
Hi Pinal, As I created Trigger as per your post, Just after 10 minutes, My site stopped working, And even no user was able to connect it neither remotely nor me locally, I used SQL CMD to connect my db & drop trigger, restart SQL Services, reset IIS and then everything was well. I want to know, What operation is performed by this trigger as my website stopped working. Even I had a lot of space on my drive.
Waiting for your reply, Thanks.
Hi Pinal when opening sql server configuration manager and click on SQL SERVER SERVICES then i am getting below error
The Remote procedure call failed[0x800706be]
I unable to stop Services which are running in SQL SERVER CONFIGURATION MANAGER
This shuts down essential services. Microsoft should fix this either by allow us to specifiy the server and remove the ALL Server parameter
or
Use a session variable to avoid re-logging.. which is available in 2016
It is not at all filtering the duplicates.