July 8, 2010 at 1:06 pm
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?
July 8, 2010 at 1:48 pm
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
July 8, 2010 at 1:57 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply