Tracking changes made thru EM

Question

Tracking changes made thru EM

Genie Cool

SSC-Addicted

Points: 478
More actions
October 15, 2007 at 7:50 am

#69257

Hi,
I have an issue. My fellow programmer once tried to change some code like commenting few lines in SPs and changing codes in Views etc thru EM in SQL 2000. The changes made thru EM (by double clicking on that file) does not update the DATE MODIFIED field. This has been very tough to find the culprit as the name of the user also does not get updated. The problem is, we know who does that.. but cannot prove. Is there any way to find or track the changes made thru EM?
Regards,
Genie Cool

Viewing 14 posts - 1 through 13 (of 13 total)

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

Strommy SSCrazy Points: 2386 More actions · Answer 1

One way to proactively do this is using SQL profiler. You can put a lot of filters on it, such as application (EM) or user, etc... The only problem is you need to have it turned on and then catch the person in the act. The other issue is it could be a lot of data to parse if you aren't good about filtering. Hope that helps.

Eric

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

There are a variety of 3rd party utilities out there that do some sort of auditing or another. Basically they are still just using a profiler trace although they tweak it quite a bit, and provide a nice front end so that you can more easily find what you are looking for.

It might not be a bad idea to demo a few tools and see if they can help you in the meantime.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

What date modified? I was under the impression the only date tracked on a stored proc is the date it was created. That only gets updated when you drop/create the proc, but not during an ALTER process. Or were you discussing a column on a table?

Also - if someone is making changes ad-hoc, isn't that a workflow/permissions process? Sounds like someone isn't following procedure. If they can't follow procedure, then perhaps they shouldn't have access to updating the server?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323504 More actions · Answer 4

if you even suspect someone is making changes without permissions, you should simply change the password(s) (probably for sa..developers connecting on production as sa a possiblity?)

then create a new role, add only the permissions they need, and give them a new login to that role...call it a security improvement to keep people's feelsing from being hurt, but it locks them out of the server. don't give them db_owner rights, just data reader ;if they need to test, they can have full rights on the development server.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

binu john SSCommitted Points: 1987 More actions · Answer 5

binu john

SSCommitted

Points: 1987

October 15, 2007 at 7:40 pm

#742197

Lovely 😉

Lanny Irwan Ten Centuries Points: 1020 More actions · Answer 6

You can create trigger on the syscolumns while cleaning up or tighten up your access rights on your database server.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

I can tell you haven't actually tried doing that... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

If the mission is to find some sort of 'evidence' of a certain action, then providing the db is not in simple recovery mode, the transaction log contains records of the alter/change.

All you need then is a tool to read the log, and the log itself.

/Kenneth

Genie Cool SSC-Addicted Points: 478 More actions · Answer 9

Hey All,

Thanks for all of the suggestions. I could not remove the permissions to the user as he seems to be a higher authority and I or either my team members do not have the right to hide or infact deny the access to him to the production server. Anyways.. Thank you for all the suggestions. We encrypted the scripts (original) and re-run the script and he was unable to do changes to the code therein. We just covered up 😉

Thanks again 🙂

Regards,
Genie Cool

Lowell SSC Guru Points: 323504 More actions · Answer 10

lol encrypting is a good answer. way to go!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Ahh - that explains it. That's one of my rules of thumb - "when dealing with a dangerous manager, make up a highly obscure technical reason, and use it to prevent them from making changes without going through you". Works every time!

"Ahh - but our secure web only deals with signed/encrypted/ procedures".

"Our rollout process now requires code to be checked into VSS to work. We don't manually touch anything anymore".

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 12

Matt Miller (10/16/2007)
Ahh - that explains it. That's one of my rules of thumb - "when dealing with a dangerous manager, make up a highly obscure technical reason, and use it to prevent them from making changes without going through you". Works every time!
"Ahh - but our secure web only deals with signed/encrypted/ procedures".
"Our rollout process now requires code to be checked into VSS to work. We don't manually touch anything anymore".

Heh... the way I fixed all of that was "Our specs say that only DBA's can promote code... you're not a DBA and I'm enforcing the specs according to company policy... take it up with HR and the security staff." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 13

Create a trace job which will secretly populate some secret table with data from master.dbo.sysprocesses where program_name = 'MS SQLEM '

Schedule it to run every minute or two.

Then at the end of the day you will have complete report about every bloody bastard who was trying to access the server using EM, including HostName, LoginName, net_address, etc.

You may add some script to save result of "dbcc inputbuffer(@SPID)" for each process inserted into the table. It will give you the text of the last command started from that connection.

Don't forget to remove records having your name in it before you pass it to the big boss.

😛

_____________
Code for TallyGenerator