Login alert

Hi all,
I want to be notified when certain users log in to SQL. I configured SQL
Mail, it works properly with several events e.g. 'Login created', but not
with "Login succeded for user '%ls' (18455). The event is set to be written
in Windows eventlog and the Audit levels is set to full. However, the alert
is not fired.
I would appreciate any idea.
Thanks,
KolosKolos,
For nontrusted alerts you need to use 18454, for trusted use, 18453. The
below script works for me for alerting me to anyone logging on using
'sa' as a non-trusted connection.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'sa login
success'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'sa login success'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'sa login success', @.message_id =
18454, @.severity = 0, @.enabled = 1, @.delay_between_responses = 60,
@.include_event_description_in = 4, @.event_description_keyword =
N'''sa''', @.category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'sa login success',
@.operator_name = N'mark', @.notification_method = 1
END
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Kolos wrote:
> Hi all,
> I want to be notified when certain users log in to SQL. I configured SQL
> Mail, it works properly with several events e.g. 'Login created', but not
> with "Login succeded for user '%ls' (18455). The event is set to be writte
n
> in Windows eventlog and the Audit levels is set to full. However, the aler
t
> is not fired.
> I would appreciate any idea.
> Thanks,
> Kolos|||1. Ensure the event is in the NT application log and that it comes from
sqlserver
2 Ensure that SQL Agent is running
3. Ensure that you have the proper alert setup and that is has either
notification or job set to run
4. Ensure that the operator or job is set up correctly
If the NT application log gets full, no more events can be added and
therefore alerts will not fire...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:41585EB8-D279-4A9D-B67E-C4F2776FA720@.microsoft.com...
> Hi all,
> I want to be notified when certain users log in to SQL. I configured SQL
> Mail, it works properly with several events e.g. 'Login created', but not
> with "Login succeded for user '%ls' (18455). The event is set to be
written
> in Windows eventlog and the Audit levels is set to full. However, the
alert
> is not fired.
> I would appreciate any idea.
> Thanks,
> Kolos