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]
Showing posts with label whom. Show all posts
Showing posts with label whom. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
audit single user mode
Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! Chuck
My bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! Chuck
My bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck
audit single user mode
Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck
audit single user mode
Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck
Subscribe to:
Posts (Atom)