Auditing SP Execute.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pranay Pandya - 23 Feb 2005 17:12 GMT

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

Reply to this Message

Andrew J. Kelly - 23 Feb 2005 18:17 GMT

You 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.

Signature

Andrew J. Kelly SQL MVP

> 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
[quoted text clipped - 3 lines]
> Thank You,
> -pranay

Reply to this Message

Pranay Pandya - 23 Feb 2005 19:42 GMT

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.

Reply to this Message

Andrew J. Kelly - 23 Feb 2005 20:54 GMT

Without monitoring the activity with either trace or a 3rd party tool there
is no way to do this in Sql2000.

Signature

Andrew J. Kelly SQL MVP

>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.

Reply to this Message

Steve Troxell - 23 Feb 2005 22:10 GMT

> Without monitoring the activity with either trace or a 3rd party tool there
> 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())

Reply to this Message

Jasper Smith - 23 Feb 2005 22:06 GMT

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)

Signature

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

> 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
[quoted text clipped - 3 lines]
> Thank You,
> -pranay

Reply to this Message

Andrew J. Kelly - 23 Feb 2005 22:20 GMT

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.

Signature

Andrew J. Kelly SQL MVP

> 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
[quoted text clipped - 10 lines]
>> Thank You,
>> -pranay

Reply to this Message

Pranay Pandya - 25 Feb 2005 15:00 GMT

Thank you Every for the posts. I am going to create the audit table and
log it.

> 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.
[quoted text clipped - 25 lines]
> >> Thank You,
> >> -pranay

Reply to this Message