June 16, 2009 at 4:45 am
While creating trigger for logon event according to BOL example as following
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
I get error following message
Msg 1084, Level 15, State 1, Procedure DenyAppUsers, Line 3
'LOGON' is an invalid event type.
Is there any requirement or setting for using logon trigger?
Regards
DBDigger Microsoft Data Platform Consultancy.
June 16, 2009 at 4:56 am
AShehzad (6/16/2009)
While creating trigger for logon event according to BOL example as following
CREATE TRIGGER connection_limit_triggerON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
I get error following message
Msg 1084, Level 15, State 1, Procedure DenyAppUsers, Line 3
'LOGON' is an invalid event type.
Is there any requirement or setting for using logon trigger?
Regards
Did you modify the trigger name for posting at SSC or is there another thing in use ??
"procedure DenyAppUsers"
Check that procedure, maybe it just materializes a "missing" event implementation.
ps: Why do you use the "execute as login_test" ??
A (non-sysadmin) connection can only see its own connection thread(s).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 16, 2009 at 5:03 am
sorry that i paste here the error message of my modified trigger. Related error message for BOL trigger (that i mentioned in code) is
Msg 1084, Level 15, State 1, Procedure connection_limit_trigger, Line 3
'LOGON' is an invalid event type.
And i have to use information for own session of 'login_test', so its ok for it to see its own connections.
Regards
DBDigger Microsoft Data Platform Consultancy.
June 16, 2009 at 5:56 am
AShehzad (6/16/2009)
sorry that i paste here the error message of my modified trigger. Related error message for BOL trigger (that i mentioned in code) isMsg 1084, Level 15, State 1, Procedure connection_limit_trigger, Line 3
'LOGON' is an invalid event type.
And i have to use information for own session of 'login_test', so its ok for it to see its own connections.
Regards
Keep in mind you need SP2 on your sql instance !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 16, 2009 at 11:48 pm
Indeed, in that case it is easy to lose track if you do it all by hand without a checklist.
Although we regularly reinstall our dba-systems-test server - and know the procedure by heart - we always use a checklist to tick installation procedings.
Keep in mind SP3 is the current service pack level ! ( and it also has some cumulative hotfixes too )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2010 at 9:37 pm
Hi,
I still got error using FOR LOGON TRIGGER, even I'd already using SQL SERVER 2005 SP3. Do I need configure something else?
The value of @@VERSION show:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
And the error message:
Msg 1084, Level 15, State 1, Procedure block_ipaddress, Line 2
'LOGON' is an invalid event type.
January 8, 2010 at 11:18 pm
Budi Hertanto (1/8/2010)
Hi,I still got error using FOR LOGON TRIGGER, even I'd already using SQL SERVER 2005 SP3. Do I need configure something else?
The value of @@VERSION show:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
And the error message:
Msg 1084, Level 15, State 1, Procedure block_ipaddress, Line 2
'LOGON' is an invalid event type.
Microsoft SQL Server 2005 - 9.00.1399.06
That is RTM !
You need SP2 of SQLServer 2005 (better SP3 off course) for Logon triggers to work !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply