• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2209
  • Last Modified:

SQL Server Logon Triggers: What Permissions Are Needed

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
mjs082969
Asked:
mjs082969
  • 5
  • 5
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
mjs082969Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
mjs082969Author Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
mjs082969Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
mjs082969Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
mjs082969Author Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now