Solved

SQL Server Logon Triggers:  What Permissions Are Needed

Posted on 2013-11-11
10
1,819 Views
Last Modified: 2014-01-03
I am creating a SQL Server Logon Trigger.  Once it was enabled, I could not logon to the system:

Logon failed for login 'xyz' due to trigger execution.
Changed database context to 'Master'.
Changed language setting to us_English (Microsoft SQL Server, Error: 17892)

I disabled the trigger.

I believe my issue to be related to permission on database objects.  The trigger reads from a table, and inserts some information in another table.  These tables are in database ABC, not the master database.

What objects do I need to give permission to?  The tables?  The database?  And who gets the permission?  Public?  Guest?

Thanks In Advance,

- Michael
0
Comment
Question by:mjs082969
  • 5
  • 5
10 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39640137
Would have to see the trigger code to offer any hard advice -- it's clearly impossible to know otherwise what the trigger's actually doing.
0
 

Author Comment

by:mjs082969
ID: 39641696
CREATE TRIGGER XYZ_Trigger ON ALL SERVER
    FOR LOGON
AS

BEGIN

      DECLARE @IPAddress NVARCHAR(50) ;

      -- Set @IP to the IP attempting access
      SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(50)') ;
                                                                                   
      -- Is IP NOT part of the 198.2.0 Subnet?
      IF @IPAddress NOT LIKE '198.2.0.%'
            BEGIN
                  -- Compare program name to 'Our Application%'  
                  IF PROGRAM_NAME() NOT LIKE Our Application%'                                      
                        BEGIN

                              -- compare the IP to the addresses in the table
                              IF NOT EXISTS ( SELECT IP FROM XYZ.dbo.ValidIPAddress WHERE IP = @IPAddress )
                                    BEGIN
                                          INSERT INTO XYZ.dbo.Login_Details (PostDateTime, IP_Address, Account, ProgName, Event_Detail, TSQL_Detail )
                                                VALUES (GETDATE(), @IPAddress, USER_NAME(), PROGRAM_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
                                          SELECT  @IPAddress
                                          ROLLBACK --Undo login process
                                    END
                              ELSE
                                    BEGIN
                                          INSERT INTO XYZ.dbo.Login_Details (PostDateTime, IP_Address, Account, ProgName, Event_Detail, TSQL_Detail )
                                                VALUES (GETDATE(), @IPAddress, USER_NAME(), PROGRAM_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
                                    END
                        END
            END
      ELSE
            BEGIN
                  INSERT INTO XYZ.dbo.Login_Details (PostDateTime, IP_Address, Account, ProgName, Event_Detail, TSQL_Detail )
                        VALUES (GETDATE(), @IPAddress, USER_NAME(), PROGRAM_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
            END
END
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39641823
Thanks!

In this case, easiest would likely be to add the "Guest" user to the XYZ database, then:
USE XYZ
GRANT INSERT, SELECT ON dbo.Login_Details TO PUBLIC

Hopefully that will do it.  If not, you might have to add the specific AD group(s) to the XYZ database.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:mjs082969
ID: 39668909
I tried the GRANT statement but it didn't work.

What might be preventing it from working?

This trigger does do a lookup succesfully.  The Trigger works until an INSERT statement is added.

Thanks Again

- Michael
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39669230
>> What might be preventing it from working? <<

What error did it get?  What does "didn't work" mean exactly?

If you're on a case-sensitive server, you might need to check the case:

--      ????
USE XYZ
--                                                          ?????????????
GRANT INSERT, SELECT ON dbo.Login_Details TO public
0
 

Author Comment

by:mjs082969
ID: 39669327
I am on a case-sensitive server, but that wasn't the cause of the issue... I had matched case.


I did read someplace that it was not advisable to have logon triggers access databases other than master and msdb.  Is this general concensus?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39669414
So, what is the error then?


>>  it was not advisable to have logon triggers access databases other than master and msdb.  Is this general concensus? <<

Yes, to insure there's no logon trigger error if for some reason the referenced db does not exist when you start up the instance (obviously master, model, msdb and tempdb will always exist, so any/all of them can safely be referenced).
0
 

Author Comment

by:mjs082969
ID: 39695784
The error is the same as identified inititally:

Logon failed for login 'xyz' due to trigger execution.
Changed database context to 'Master'.
Changed language setting to us_English (Microsoft SQL Server, Error: 17892)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39695948
Often that error means a referenced db does not exist, or that login has no authority to that db.

Probably want to explicitly grant connect just in case:

USE XYZ
GRANT CONNECT ON dbo.Login_Details TO public

Also, check whether the dbo.Login_Details table has a trigger that fires on INSERT that attempts to access/modify any other tables.
0
 

Author Comment

by:mjs082969
ID: 39754453
I did attempt to explicitly grant, but this did not work either.

The solution I implemented was not wht I had hoped it would be.  I was hoping to log information into a table from the login trigger.  For now, I was able to accomplish what needed to be done.  Thanks for all of your assistance; I do hope to attempt to implement this again in the near future.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question