Solved

SQL Server Logon Triggers:  What Permissions Are Needed

Posted on 2013-11-11
10
1,929 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 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