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/)
Showing posts with label audit. Show all posts
Showing posts with label audit. Show all posts
Friday, March 30, 2012
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
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/)
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 audit local access to sql server
hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpf
You can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegr oups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>
|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?
|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegr oups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpf
You can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegr oups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>
|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?
|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegr oups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>
how to audit local access to sql server
hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpfYou can set up a rolling serverside trace that filters on the hostname of
the local server
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegroups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegroups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpfYou can set up a rolling serverside trace that filters on the hostname of
the local server
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegroups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegroups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>
how to audit local access to sql server
hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpfYou can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegroups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegroups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpfYou can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegroups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegroups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>
How to Audit DMLs in SQL Server 2005
Hi,
sql
I am looking for solutions of auditing DMLs using SQL Server 2005.
The tasks are:
1) Audit DML statements, including SELECT, INSERT, UPDATE, and DELETE. Please note, not only UPDATE, DELETE and INSERT, but also SELECT
2) Protect the audit trails from insider threats, i.e. protect the audit trails from DBAs.
The questions are:
What would be the solutions recommended by MS?
What are the options, either good or bad, to accomplish auditing DML and protecting audit trails.
Thanks a lot
The most 'robust' and secure method is utilzing one of the third party products designed for that purpose. Any method utilizing server based triggers or stored procedures 'could' be vunerable to DBA mis-use.
Here are links to most of them -there could be additional not on my list.
Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Adit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/
|||Hi, Arnie,
Thanks a lot for your prompt response.
I checked out these products and found they can audit INSERT, UPDATE, and DELETE, most via triggers, but they can not audit SELECT.
Please forgive me for a few more questions in that direction:
1) Is there any built-in features of SQL Server 2005 for DML auditing, except database triggers?
2) Can SQL Profiler be used for auditing SELECT, INSERT, UPDATE and DELETE statements?
3) Is there any way to audit SELECT using SQL Server 2005?
Thanks a lot again :-)
|||
I don't think that is exactly correct. Lunigent's Audit tool reads the Transaction log -does not rely on database Triggers, AND can easily track SELECT activity.
You should check it out a bit more. Not inexpensive, but it will be less than the time cost to attempt to create anything that does the job, and whatever is custom created is guaranteed to be less 'robust'.
http://lumigent.com/products/auditdb.html
Your questions...
1. No
2. Yes, but with a measurable performance hit. And would be easily accessible by a DBA since it requires the same level of access to the server as a DBA.
3. No, not directly. SELECT statement 'could' be done through stored procedures, and INSERTs make to a logging table, but again, suseptible to DBA interference.
If you want to be able to log priviledged users (DBA, DBO, etc.) AND data reads, I don't know of any 'secure' method other than the third party products.
|||That is great to know that SQL Profiler can audit SELECT, INSERT,UPDATE and DELETE.
Assuming we ignore the requirement about protecting auditing trails for this moment:
1) Which event(s) should I audit SELECT, UPDATE, DELETE and INSERT against a table using SQL Profiler?
2) Can we audit the (before and after) values of UPDATE, DELETE and INSERT on a table in a trace log?
3) Also I am a little confused: while saying SQL Server 2005 doesn't have built-in auditing DML feature (except triggers), however, SQL Profiler does audit SELECT, UPDATE, DELETE and INSERT. Is there any thing wrong in auditing DML in SQL Profiler, except performance issue? I am not sure if we could say or use SQL Profiler to audit DML.
Yes, I double checked Lunigent's Audit tool, it does audit all DMLs.
Thanks a lot for that information.
I am very new to SQL Server, please forgive me for asking so much questions
|||
Profiler can capture the command and parameters/values that were sent to SQL Server. It does not, however, provide the results (or 'after) state due to data modification -UNLESS there is another specific data read.
Events: TSQL: StmtStarting, TSQL: StmtCompleting
Profiler wasn't designed as a 'Auditing' tool.
Profiler is a bit 'ham handed' compared to the tools specifically designed for auditing.
Profiler requires the same level of permissions as a DBA would have.
Profiler can be easily 'thwarted' by a DBA.
AS you stated your specifications, I highly recommend NOT going further with Profiler for the Auditing task.
You will be investing a lot of time and effort, and it still will not meet your requirements.
|||Thank you very much, Arnie, your comments are very helpful...
sql
How To Audit DML Table Changes w/o Triggers?
Whenever any DML activity occurs in a database I need to audit the following:
1. The table that was changed (INS, UPD, DEL)
2. The data value of the primary key of the changed row
For example, if someone executes:
UPDATE payroll SET Salary = 50000 WHERE empid = 123
I need: "payroll" and "123"
For various business reasons I can't use triggers. It is just one of those things...
I've looked at various options on SQL Profiler and while it looks like I can tell there was an action on the "payroll" table it doesn't look likely that I'll be able to figure out that it was on primary key 123. This seems to especially be the case on the execution of a stored proc where data values are passed as @.parameters.
I know there are third-party products that analyze transaction logs with a GUI and let you export these results to a CSV file or Excel. The concern here is that I need something which is an on-going process, so the GUI and human interaction necessary to generate the file doesn't quite cut it. If I'm wrong here and there's a suggestion I'd certainly look at it.
Thanks so much!
Doug
The options are pretty much what you described. Or you could do this in your code by logging the parameters to the SP that does the modification for example. Allowing ad-hoc insert/update/delete to tables directly is not a good thing to do. In SQL Server 2005, you can use event notifications to do this easily.|||Thanks for the feedback...but I'm not sure I understand you ... I cannot use a trigger ... and the log reading tools don't seem to offer a constant flow. So the items I mention in my post won't work.Is there any other approach?
Thanks again...I appreciate any suggestions.
Doug|||My point was that your options are limited in SQL Server 2000. I am not aware of the fulll capabilities of various 3rd party tools that work with log files directly. Did you check web site of companies like Lumigent? Another idea I was thinking about was to use replication. You could configure log reader agent to output verbose information which will include commands that are being replicated. Maybe this will help. It is hard to tell. And it almost seems like you have to modify your application if existing tools or methodologies do not meet your requirements.|||OK, this gives me a couple of leads to follow. Thank you very much for thinking this over with me, I appreciate your feedback.
All the best!
Doug
How to audit data flow runs
What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.
There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.
-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.
http://markiehillmsis.blogspot.com/
Thanks
Sutha
How to audit data flow runs
What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.
There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.
-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.
http://markiehillmsis.blogspot.com/
Thanks
Sutha
How to audit all SQL Queries in a database?
How can you audit all queries within a database?
Hi Joshua,
there is builtin functionality for this. Youcan either implement your own logic at your frontend for doing so, using an additional layer between the database on the data components or only use stored procedure to retrieve the data with logging the actions inside the stored procedure.
If you want to use a third party logreader like lumigent you can explore the transaction logs for statements.
-Jens Suessmeyer.
http://www.sqlserver2005.de
Hi Joshua,
Look for SQL Trace or Profiler in Books Online.
|||Audit sounds to me (could be to my non-native language english) as you want to audit or trace SQL queries for a long time. profiler is very costy and should only be started as a debugging tool, not for running it all the time. I just wanted to make sure that you keep that in mind. Otherwise, using the Profiler for a short amount of time to find out problems and queries fired against the database is a usual and daily business for developers and administrators.HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
If your interest this in the control of changes in the registries of a table, you can use triggers to before know the state the data of the registry and despues of being updated.
Respuesta en idioma original:
Si tu interes esta en el control de cambios en los registros de una tabla, puedes utilizar disparadores para conocer el estado de los datos del registro antes y despues de ser actualizado.
|||Profiler is very costly, but you can set up a job to run a trace, just for certain types of events, like Batch Complete and RPC Complete, which will capture all of the queries run. If you script this in a job, then direct the results to a trace file on the hard drive of your server, the cost is minimal. You can then use fn_get_traceinfo to review the trace files, and even load them into a database table for analysis.|||Thanks Allen, this was helpful. I'll give it a try.
Subscribe to:
Posts (Atom)