Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to audit changes in permissions Expand / Collapse
Author
Message
Posted Wednesday, March 15, 2006 9:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 1:50 PM
Points: 76, Visits: 10

My SOX requirements are that I need to monitor when any changes are made to user privileges - if someone is granted new access, etc. Ideally also when a new user is created.

I have a trace running from SQL Profiler now but that is a pain because everytime the server is rebooted I have to stop the trace, save thefile and start a new trace.

I have to monitor this on 6 different servers.

Does anyone know of a better way to monitor this? Procedures or third part software?

 

Thanks.

Post #265971
Posted Thursday, March 16, 2006 8:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 11, 2009 7:12 AM
Points: 75, Visits: 4

While I've never done this personally, it still may be a valid solution for you. Have you considered using server-side tracing, such as with sp_trace_create? Once that is created you could create a job that starts when SQL Agent starts (presumably on startup) that would run "sp_trace_status @traceid, 1" to start the trace. Hope that helps.

Brian

Post #266252
Posted Thursday, March 16, 2006 12:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:33 PM
Points: 1,034, Visits: 205

We looked at doing a server side trace which worked. We decided for time and effort that it was better to purchased DB Audit. It does a server side trace but has a simple gui interface to view reports. Very easy to use

 

Post #266351
Posted Tuesday, March 27, 2007 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 7, 2007 7:36 AM
Points: 2, Visits: 2
We did a server side trace as well. We use a SQL job with a DTS package to stop the traces, import the data to tables (for reporting purposes later) and then restart the traces every hour.
Post #354157
Posted Sunday, May 6, 2007 10:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:00 AM
Points: 4, Visits: 164
Rick,

can you post more information on how you accomplished your tasks? code would be great!

thanks
Post #363568
Posted Tuesday, May 8, 2007 6:08 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:03 AM
Points: 486, Visits: 1,221

I have a similar situation.

I plan to look into the "SQL Compliance Manager" product by Idera. I haven't evaluated it yet, other than reading their datasheet, so I can't offer an opinion about it one way or another.

http://www.idera.com/Products/SQLcm/

I currently use their SQLdm SQL Diagnostic software and have been pleased with it.  Like many of these types of products, they don't really tell you things you can't obtain otherwise, but they wrap them up into a convenient package that is easy to use.  So if you're not into "rolling your own" DB utilities and can spend some money it may be a reasonable solution.




Post #363948
Posted Tuesday, May 26, 2009 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 4, 2013 6:39 AM
Points: 363, Visits: 417
Instead of using profiler trace, using event notification we can store records for required events in a table. Laster, using SSRS a report can be developed which displays changes done...
Post #723235
Posted Tuesday, May 26, 2009 7:36 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,432, Visits: 4,171
nitin.doshi (5/26/2009)
Instead of using profiler trace, using event notification we can store records for required events in a table. Laster, using SSRS a report can be developed which displays changes done...

I agree.
Event Notifications would probably be the best option without using third-party tools but since this is posted in the SQL 2000 forum this would not be available.


Markus Bohse
Post #723244
Posted Tuesday, May 26, 2009 10:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 4, 2013 6:39 AM
Points: 363, Visits: 417
HI Markus,

I agree that for 2000 we need to do it by profiler or third party tool.

For 2005, for events like security changes/schema changes I have tested using Notification. But could not find anything to audit changes like job creation/updation/deletion. Do U know how to monitor the same...
Post #723770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse