Very Urgent Help Needed related to security

Question

Very Urgent Help Needed related to security

RPSQL

Hall of Fame

Points: 3229
More actions
June 5, 2010 at 9:54 am

#225353

Here is the brief description of our issue...
I am lead DBA working in a very large environment with other 7 dba's.
I got issues in past that permission of some account changed in production and due to that application failed/were not able to function properly.
We got same issue today..There was a job which was running fine since years and suddenly it failed. That was a manufacturing job and very critical...I found out that it was issue related to permission, on of the user which was having dbowner permission on a database and that permission was taken away.
I need help to find out how that permission was changed ,when that permission was changed and who did that. As we are total 8 dba's having 'sa' access on these servers this is really rough to find out.
Please help me out...this is very serious issue...it happend many times in past and this is critical for us to know who do this?
Thanks very much in Advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Unless you have already established auditing processes to capture these types of changes, you may not be able to determine the who and when this occurred.

You may want to start reading about auditing in BOL, it should give you a good idea where to start going forward. Just remember, it isn't a catch all as someone could disable the audting, make changes, and reenable the auditing; or just change/delete the data after the fact if they have the access to do so.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

The default trace might contain this, since it does capture object creation and drops. You'll have to check it out to see if it still exists.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

There are tools such as Compliance Manager that will track changes going forward, or you could create your own server side trace, and have it restart when the service gets restarted.

ashish 10950 SSC-Addicted Points: 422 More actions · Answer 4

You need to set Database Auditing using DDL trigger in sql server 2005 ...

for checking data (crud operation) you can use a free tool called SSMS TOOLS 7.0 free available on net , just download and use . I tried to track the security from tools but was not possible... So go for DDL trigger .

Regrads

Ashish Gupta

LutzM SSC Guru Points: 107049 More actions · Answer 5

But whatever you do, keep in mind that someone with sa privileges might still be able to change permissions without leaving a trace (or at least with a trace that's hard to find). It'll just take increased effort to remain "unknown".

One of the main questions is: were those changes made being unaware of the consequences (e.g failed jobs) or has it been done purposely? I truly hope it's the former though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

You can also set up to send mail with required information as soon as DDL trigger fires.

I hope as a DBA no body is doing like this deliberately. But it's good to find out root cause.

The one possibility of doing such thing is after the restoring the database on the server; orphan users need to fix and that may create a problem.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

tpaulsen SSC Enthusiast Points: 195 More actions · Answer 7

Another possibility (and it is a real long shot) is if the job has a very tight window on time between when it succeeded and when it failed and you are using windows accounts to access the databases, the event viewer on the sql server can tell you who was logged in to that server at the time (but that is about it). Your are not able to see what the logged on user was doing on the server without some form of auditing/DDL triggers setup beforehand. The Windows logs are about the only thing you can truly protect from users with sa privileges. The new auditing in sql 2008 would be a great tool to log further changes as it even logs when and who turns the auditing off (If the changes are being done intentionally and someone is trying to hide their tracks).