After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:
CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
DECLARE @data XML
SET @data = EVENTDATA()
EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
GETDATE(),
CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(NVARCHAR(100), HOST_NAME()),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
) ;
REVERT
END
As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.
Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.
My team lead, pointed out the issue. As I could not find the issue/solution here, posting it for others.
I knew I could not use windows authentication, so I was trying to use SQL Server Authentication, this was the problem.
To access SSMS as a different user - When opening SSMS from your PC; Shift + right click > ‘Run as Different User’ enter ID ‘Admin1’ + password, proceed as normal.
Best Answer
You should disable your trigger:
You should be able to do it without problem if you log in as
sysadmin
because if your trigger is exactly as you posted, it will be executed without errors bysysadmin
.Or you should do it using
DAC
, i.e. you should connect to server using-A
, in case your trigger is different and evensysadmin
cannot login now.Here you can find step to step instruction with screenshots: SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem.
Your problem can be the following: your trigger tries to insert into a table in
master
database but usually users don't have any permission inmaster
. Unless the login issysadmin
or is mapped tomaster
explicitely, it isguest
in master and has no permission on any user table inmaster
.To fix the issue you can use
execute as
clause in yourlogon trigger
, this way the trigger will be executed with the permissions of the login that you put inexecute as
clause.One osservation: if all you wanted to do is to record every successful login to server you can do it by changing
login auditing
:This way you'll get all the successful logins in
SQL Server errorlog
.