Logon Trigger fails on SQL Server Authenctication

Question

Post reply

Logon Trigger fails on SQL Server Authenctication

oskargunn

SSCommitted

Points: 1951
More actions
November 10, 2010 at 4:51 am

#147553

I have a very simple logon trigger on SQL 2005.
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDatabase.dbo.LogonHist
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
A login as Windows Authentication produces one row in the LogonHist table.
However if I log on as a SQL Server Authentication user I get a logon error:
Cannot connect to <my machine name>
Additional information:
Logon failed for login 'test' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. ( Microsoft SQL Server, Error: 17892 )
The sa user works though.... and if I assign sysadmin server role to the test user it also works....
Has anybody encountered this ? :/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Does the "test" user have permissions on AuditDatabase database?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 2

This means that "Test" User is unable to execute the Trigger,

Create TRIGGER Tr_ServerLogon

ON ALL SERVER WITH Execute As 'sa' FOR LOGON

AS

BEGIN

INSERT INTO AuditDatabase.dbo.LogonHist

SELECT ORIGINAL_LOGIN(),USER,@@SPID,GETDATE()

END

GO

1. Execute As 'sa' will make sure that Trigger can be executed,

2. Original_Login() will return Original Login Name Used, (Test in Your Case).

Vishal Gajjar
http://SqlAndMe.com

oskargunn SSCommitted Points: 1951 More actions · Answer 3

Ah that´s the case 🙂

I need to add a "WITH EXECUTE as..." clause in the trigger definition

Now it works, thanks 🙂

AK1516 SSCrazy Points: 2705 More actions · Answer 4

can you please explain how is this change handled in sql server ?

Lowell SSC Guru Points: 323518 More actions · Answer 5

the other option, instead of EXECUTE AS, is to GRANT INSERT ON AuditDatabase.dbo.LogonHist to PUBLIC;

then every user would technically have rights to the table so the trigger can isnert into it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!