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?
>
Showing posts with label logging. Show all posts
Showing posts with label logging. Show all posts
Friday, March 30, 2012
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 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
Subscribe to:
Posts (Atom)