Auditing SP Execute.

To meet audit requirements, I have to maintain an audit log of when a
particular sp was execute and by whom. I am not willing to use the c2
option for just one stored procedure. There are 2 users who can run
the stored procedure from enterprise manager or query analyser (others
run it from an app). Any suggetions?
Thank You,
-pranayYou can run a trace that is filtered only on this sp. That is the easiest
and least expensive way. Or you would have to buy one of the 3rd party log
tools to view the contents of the log.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||I want to capture the user name and the time (using sql). i dont want
to run profiler for ever. When someone runs the sp may be i can get
the userid and insert it into a table.|||Without monitoring the activity with either trace or a 3rd party tool there
is no way to do this in Sql2000.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109187761.063534.20330@.z14g2000cwz.googlegroups.com...
>I want to capture the user name and the time (using sql). i dont want
> to run profiler for ever. When someone runs the sp may be i can get
> the userid and insert it into a table.
>|||As long as the users only have execute permissions on the proc i.e. they
can't change it, then just add some code at the start of the proc to log to
a table passing getdate() and suser_sname() to capture the time and user
executing the proc. This assumes that the app doesn't use a single service
account (in which case you won't be able to get the user name without
modifying the app to stuff it into context_info)
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
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||Andrew J. Kelly wrote:
> Without monitoring the activity with either trace or a 3rd party tool ther
e
> is no way to do this in Sql2000.
>
Of course he can.
create table AuditLog (WhenItRan datetime, WhoRanIt sysname)
go
Add to beginning of SP:
insert into AuditLog values (getdate(), suser_sname())|||Ahh yes. My original reading was he was looking to trace the sp_executesql
sp but I think I was mistaken. If it is a user sp then sure.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
> As long as the users only have execute permissions on the proc i.e. they
> can't change it, then just add some code at the start of the proc to log
> to a table passing getdate() and suser_sname() to capture the time and
> user executing the proc. This assumes that the app doesn't use a single
> service account (in which case you won't be able to get the user name
> without modifying the app to stuff it into context_info)
> --
> 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
> "Pranay Pandya" <ppandya@.gmail.com> wrote in message
> news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
>|||Thank you Every for the posts. I am going to create the audit table and
log it.
Andrew J. Kelly wrote:
> Ahh yes. My original reading was he was looking to trace the
sp_executesql[vbcol=seagreen]
> sp but I think I was mistaken. If it is a user sp then sure.
> --
> Andrew J. Kelly SQL MVP
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
they[vbcol=seagreen]
to log[vbcol=seagreen]
and[vbcol=seagreen]
single[vbcol=seagreen]
name[vbcol=seagreen]
when a[vbcol=seagreen]
the c2[vbcol=seagreen]
run[vbcol=seagreen]
(others[vbcol=seagreen]