Server level logon Trigger is not executing

Question

Server level logon Trigger is not executing

Minaz Amin

SSChampion

Points: 11052
More actions
August 27, 2015 at 11:10 am

#302679

Hi all,
I have server level logon event trigger which should run a proc if I create a login. But this is not getting executed. This is running in my local server but not in QA server. IN my QA server I am sysadmin in sql server.
Thanks
"More Green More Oxygen !! Plant a tree today"

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

we'd have to see your trigger. is your trigger enabled? is it referencing the procedure by full path, ie master.dbo.yourproc?

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Hang on...

A logon trigger fires when someone logs in to the server, not when a login is created. You'd need a server-scope DDL trigger to fire on CREATE LOGIN. Which do you have?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

I am having DDL trigger with server scope. The same trigger gets executed on a local box with 2014 without AAG configured , but If I ran the code on QA box with AAG configured this trigger does not work. Please find the trigger script attached.

The trigger get fired each time we create a login on SQL Server and the trigger will call a proc to add the login info in a table with SID and PWD.

I am facing issue when I ran the same code on QA with AAG configured.

"More Green More Oxygen !! Plant a tree today"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

How about the code for the proc...testme.[dbo].[addlogin]?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

it adds the new logins in the table with create date. The same trigger am testing on local it is working but not firing on QA client box. Do you think this might be due to permission issue?

USE [testme]

GO

/****** Object: StoredProcedure [dbo].[addlogin]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[addlogin] @lv_obj_name varchar(500)

AS

Begin

IF len(@lv_obj_name) >0

begin

insert into [testme].[dbo].[Login_tab]

values (@lv_obj_name,GETDATE())

--print ' added to login table';

end

Else

Begin

print 'NULL value added'

end

"More Green More Oxygen !! Plant a tree today"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Minaz Amin (8/30/2015)
[dbo].[addlogin]
[testme].[dbo].[Login_tab]

We presume these objects exist and you are not getting any sort of error message?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events