SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

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 (http://blog.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

  1. Pingback: SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’. Journey to SQL Authority with Pinal Dave

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

    Can you help me on that as I am new to trigger.
    Thanks in advance

    Like

  3. Hi Pinal!
    Actually when I create logon triger I normally use evendata() method of Sql Server is that right or not.

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  9. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  10. 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 – http://beyondrelational.com/blogs/nakul/archive/2011/01/17/the-multiple-connections-of-ssms.aspx

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

    Like

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

    Like

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

    Like

    • 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

      Like

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

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

    Like

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s