How to Audit Login, Log off, and Login failure attempts

Hi,
Can anyone provide a pointer on how to enable login, log out, and=
login failure attempts without using the SQL Profiler? I want=
to enable this type of auditing at all times and store the=
results to a table. SQL Profiler does it however I would have=
to keep this tool open all the time in order to get the audit=
results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)Take a look at sp_trace_create and sp_trace_status in Books Online. You can
create a SQL Agent job to start these on startup or create your own procedur
e
that preps and calls these and mark that proc to start up when SQL Server
does. It basically does the same thing as profiler but is script based
instead of the interactive GUI.
Hope this helps.
Sincerely,
Anthony Thomas
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure at
tempts without using the SQL Profiler? I want to enable this type of auditing at al
l times and store the results to a table. SQL Profiler does it however I would have
to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Hi,
See this article by Vyas.
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Thanks
Hari
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:7938CF87-6D42-43FD-8A54-7F7288BEE53A@.microsoft.com...[vbcol=seagreen]
> Take a look at sp_trace_create and sp_trace_status in Books Online. You
> can
> create a SQL Agent job to start these on startup or create your own
> procedure
> that preps and calls these and mark that proc to start up when SQL Server
> does. It basically does the same thing as profiler but is script based
> instead of the interactive GUI.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Guest" wrote:
>|||Also in SQL Enterprise Manager, right click your server and go to properties
( I think the security tab) ... IN the middle you can set some login
auditing parameters..
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
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23prHz3gwEHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
Can anyone provide a pointer on how to enable login, log out, and login
failure attempts without using the SQL Profiler? I want to enable this type
of auditing at all times and store the results to a table. SQL Profiler
does it however I would have to keep this tool open all the time in order to
get the audit results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)|||What you require is also what C2 auditing in SQL Server offers (and much
more). This might also help:
http://www.databasejournal.com/feat...cle.php/3399241
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure at
tempts without using the SQL Profiler? I want to enable this type of auditing at al
l times and store the results to a table. SQL Profiler does it however I would have
to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Server side tracing is exactly what I need. Thank you for all your response
s!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)

How to Audit Login, Log off, and Login failure attempts

Hi,
Can anyone provide a pointer on how to enable login, log out, and= login failure attempts without using the SQL Profiler? I want= to enable this type of auditing at all times and store the= results to a table. SQL Profiler does it however I would have= to keep this tool open all the time in order to get the audit= results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)Take a look at sp_trace_create and sp_trace_status in Books Online. You can
create a SQL Agent job to start these on startup or create your own procedure
that preps and calls these and mark that proc to start up when SQL Server
does. It basically does the same thing as profiler but is script based
instead of the interactive GUI.
Hope this helps.
Sincerely,
Anthony Thomas
"Guest" wrote:
> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Hi,
See this article by Vyas.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Thanks
Hari
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:7938CF87-6D42-43FD-8A54-7F7288BEE53A@.microsoft.com...
> Take a look at sp_trace_create and sp_trace_status in Books Online. You
> can
> create a SQL Agent job to start these on startup or create your own
> procedure
> that preps and calls these and mark that proc to start up when SQL Server
> does. It basically does the same thing as profiler but is script based
> instead of the interactive GUI.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Guest" wrote:
>> Hi,
>> Can anyone provide a pointer on how to enable login, log out, and login
>> failure attempts without using the SQL Profiler? I want to enable this
>> type of auditing at all times and store the results to a table. SQL
>> Profiler does it however I would have to keep this tool open all the time
>> in order to get the audit results.
>> Any advice is greatly appreciated. Thanks!
>> -Lorinda
>> User submitted from AEWNET (http://www.aewnet.com/)|||Also in SQL Enterprise Manager, right click your server and go to properties
( I think the security tab) ... IN the middle you can set some login
auditing parameters..
--
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
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23prHz3gwEHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
Can anyone provide a pointer on how to enable login, log out, and login
failure attempts without using the SQL Profiler? I want to enable this type
of auditing at all times and store the results to a table. SQL Profiler
does it however I would have to keep this tool open all the time in order to
get the audit results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)|||What you require is also what C2 auditing in SQL Server offers (and much
more). This might also help:
http://www.databasejournal.com/features/mssql/article.php/3399241
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Guest" wrote:
> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Server side tracing is exactly what I need. Thank you for all your responses!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)sql

How to Audit Login, Log off, and Login failure attempts

Hi,
Can anyone provide a pointer on how to enable login, log out, and=
login failure attempts without using the SQL Profiler? I want=
to enable this type of auditing at all times and store the=
results to a table. SQL Profiler does it however I would have=
to keep this tool open all the time in order to get the audit=
results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)
Take a look at sp_trace_create and sp_trace_status in Books Online. You can
create a SQL Agent job to start these on startup or create your own procedure
that preps and calls these and mark that proc to start up when SQL Server
does. It basically does the same thing as profiler but is script based
instead of the interactive GUI.
Hope this helps.
Sincerely,
Anthony Thomas
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>
|||Hi,
See this article by Vyas.
http://vyaskn.tripod.com/server_side...sql_server.htm
Thanks
Hari
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:7938CF87-6D42-43FD-8A54-7F7288BEE53A@.microsoft.com...[vbcol=seagreen]
> Take a look at sp_trace_create and sp_trace_status in Books Online. You
> can
> create a SQL Agent job to start these on startup or create your own
> procedure
> that preps and calls these and mark that proc to start up when SQL Server
> does. It basically does the same thing as profiler but is script based
> instead of the interactive GUI.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Guest" wrote:
|||Also in SQL Enterprise Manager, right click your server and go to properties
( I think the security tab) ... IN the middle you can set some login
auditing parameters..
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
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23prHz3gwEHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
Can anyone provide a pointer on how to enable login, log out, and login
failure attempts without using the SQL Profiler? I want to enable this type
of auditing at all times and store the results to a table. SQL Profiler
does it however I would have to keep this tool open all the time in order to
get the audit results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)
|||What you require is also what C2 auditing in SQL Server offers (and much
more). This might also help:
http://www.databasejournal.com/featu...le.php/3399241
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>
|||Server side tracing is exactly what I need. Thank you for all your responses!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)

How to analyze SQLProfiler trace result ?

Hi All,
I plan to run SQL Profiler because my SQL Server 2000 get the users
timed out after running some time, and happen every day.
(see
[url]http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thr
ead/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b[/u
rl])
The question is :
After I get the trace recorded using Profiler, how can I check which
query that cause the problem ?
Or what I see any clue of the problem on the SQL Profiler result ?
Thank you very much,
xtantoA timeout is called "Attention" in Profiler. So make sure you catch that eve
nt. You can now go
backward for a SPID, from the Attention to the SQL:BatchStarted (or similar)
event to see what query
was timed out.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<krislioe@.gmail.com> wrote in message news:1155353854.603424.64300@.74g2000cwt.googlegroups.c
om...
> Hi All,
> I plan to run SQL Profiler because my SQL Server 2000 get the users
> timed out after running some time, and happen every day.
> (see
> [url]http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_t
hread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b[
/url])
> The question is :
> After I get the trace recorded using Profiler, how can I check which
> query that cause the problem ?
> Or what I see any clue of the problem on the SQL Profiler result ?
> Thank you very much,
> xtanto
>

How to analyze SQLProfiler trace result ?

Hi All,
I plan to run SQL Profiler because my SQL Server 2000 get the users
timed out after running some time, and happen every day.
(see
http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b)
The question is :
After I get the trace recorded using Profiler, how can I check which
query that cause the problem ?
Or what I see any clue of the problem on the SQL Profiler result ?
Thank you very much,
xtantoA timeout is called "Attention" in Profiler. So make sure you catch that event. You can now go
backward for a SPID, from the Attention to the SQL:BatchStarted (or similar) event to see what query
was timed out.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<krislioe@.gmail.com> wrote in message news:1155353854.603424.64300@.74g2000cwt.googlegroups.com...
> Hi All,
> I plan to run SQL Profiler because my SQL Server 2000 get the users
> timed out after running some time, and happen every day.
> (see
> http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b)
> The question is :
> After I get the trace recorded using Profiler, how can I check which
> query that cause the problem ?
> Or what I see any clue of the problem on the SQL Profiler result ?
> Thank you very much,
> xtanto
>|||Hi Tibor,
Thanks for your answer.
Actually what I want to find with Profiler is not the query that was
timed out, because when the "specific condition" met, "any queries" to
the server, from "any user" will get timed out.
So my goal is to find What Has caused this "specific condition" ?
Thank you very much,
xtanto
Tibor Karaszi menuliskan:
> A timeout is called "Attention" in Profiler. So make sure you catch that event. You can now go
> backward for a SPID, from the Attention to the SQL:BatchStarted (or similar) event to see what query
> was timed out.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <krislioe@.gmail.com> wrote in message news:1155353854.603424.64300@.74g2000cwt.googlegroups.com...
> > Hi All,
> >
> > I plan to run SQL Profiler because my SQL Server 2000 get the users
> > timed out after running some time, and happen every day.
> > (see
> > http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b)
> >
> > The question is :
> > After I get the trace recorded using Profiler, how can I check which
> > query that cause the problem ?
> > Or what I see any clue of the problem on the SQL Profiler result ?
> >
> > Thank you very much,
> > xtanto
> >