Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to audit changes in permissions


Need to audit changes in permissions

Author
Message
Sharif-217569
Sharif-217569
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
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.


Brian Fenton
Brian Fenton
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
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


Erick Bailey
Erick Bailey
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 309

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


Rick Binns
Rick Binns
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Jigar Lakhani
Jigar Lakhani
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 164
Rick,

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

thanks
Ed Zann
Ed Zann
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 1391

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.





nitin.doshi
nitin.doshi
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 432
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...
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4457 Visits: 4208
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
nitin.doshi
nitin.doshi
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 432
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search