Hi,
I need to have something like a "login event" so that whenever a user logs
into SQL server, I can execute certain commands or SPs. So far, I've been
using this event in my client app that when a user clicks on the login
button, I do those jobs. But I want SQL server to handle and perform these
activities automatically and recognize each login.
Any help would be greatly appreciated.
AminTHere is no such event inside SQL Server.
AND you can't put triggers on SYSprocesses (which would be the next thing to
try).
You can write a proc that watches sysprocesses for new connections, or
you can catch logins with profiler and write a program that watches the
profiler output or
you can catch logins in the SQL error log (turn this on via SQL Enterprise
Manager on the Server->security tab and write a program that watches the SQL
log but
NONE of these will allow you to run proces etc on the newly connected
thread...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23qLxNBACEHA.3784@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need to have something like a "login event" so that whenever a user logs
> into SQL server, I can execute certain commands or SPs. So far, I've been
> using this event in my client app that when a user clicks on the login
> button, I do those jobs. But I want SQL server to handle and perform these
> activities automatically and recognize each login.
> Any help would be greatly appreciated.
> Amin
>
Showing posts with label certain. Show all posts
Showing posts with label certain. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
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,
Kolos
Kolos,
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 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
|||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
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
Kolos,
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 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
|||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
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 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|||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
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 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|||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
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
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
Subscribe to:
Posts (Atom)