LOGON Trigger and SQLCMD question

Question

LOGON Trigger and SQLCMD question

SumOfDavid

Ten Centuries

Points: 1357
More actions
July 8, 2010 at 1:06 pm

#223068

I am implementing a LOGON Trigger. Nothing fancy. Here's the code:
USE master
GO
-- create table
CREATE TABLE [dbo].[Log_ServerLogon]
(
[EventTime] datetime2(7) NULL,
[EventType] varchar(100) NULL,
[LoginName] varchar(100) NULL,
[HostName] varchar(100) NULL,
[AppName] nvarchar(128) NULL,
[SPID] int NULL
)
GO
-- create trigger
CREATE TRIGGER tri_Log_ServerLogon
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @msg xml
SET @msg = eventdata()
INSERT INTO master.dbo.Log_ServerLogon
(
EventTime,
EventType,
LoginName,
HostName,
AppName,
SPID
)
VALUES
(
SYSUTCDATETIME(),
CAST(@msg.query('/EVENT_INSTANCE/EventType/text()') AS varchar(100)),
CAST(@msg.query('/EVENT_INSTANCE/LoginName/text()') AS varchar(100)),
CAST(@msg.query('/EVENT_INSTANCE/ClientHost/text()') AS varchar(100)),
APP_NAME(),
@@SPID
)
END
GO
Nothing special going on here. When I run this in SSMS, everything works perfectly.
My problem is that I actually have the table creation in one script file and the trigger creation in another script file. (a development methodology I use).
I then have a .bat file which executes these script files via SQLCMD.
My batch file basically looks like this:
sqlcmd -E -S %1 -d %2 -i "CREATE TABLE Log_ServerLogon.sql" -o output.log
sqlcmd -E -S %1 -d %2 -i "CREATE TRIGGER tri_Log_ServerLogon.sql" >> output.log
I use a command prompt and type xxxx.bat [server] [database] and it executes.
Everything gets installed fine, EXCEPT, that the logon trigger basically locks me out of the server no matter how I log in. (the error message tells me it's the trigger that's preventing me from connecting). I then have to log in on the server using the sqlcmd -A switch to drop the trigger.
Can somebody explain why this happens this way?
David

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

just checking the basics...SYSUTCDATETIME() is SQL 2008 only...could it be the server you ran the script on is 2005 and the trigger is failing with invalid column name?

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!

SumOfDavid Ten Centuries Points: 1357 More actions · Answer 2

No, the server is SQL 2008.

The script works fine (the auditing works fine) if I execute the scripts through SSMS.

The objects get created if I execute them via the batch command, but the trigger locks everybody out.

So, my question is why the trigger locks everybody out when I create it via sqlcmd, but not SSMS.

David