May 20, 2002 at 5:34 pm
Hi,
We are having some issues with people changing database objects (such as stored procs) and thus breaking the front-end application. What I want to know is: is there any way I can periodically check to see if a db object (stored proc, view, etc) has been changed and which SQL Server login changed it? Also, can I find out what change was made?
We can't use 3rd party tools like Log Explorer (much as I want to), so this needs to be a purely SQL Server method.
Any help would be very much appreciated.
Owen
Owen West
Programmer,
Hunter Area Health Service
Owen West
Programmer,
Hunter New England Health
May 20, 2002 at 5:51 pm
quote:
Hi,We are having some issues with people changing database objects (such as stored procs) and thus breaking the front-end application. What I want to know is: is there any way I can periodically check to see if a db object (stored proc, view, etc) has been changed and which SQL Server login changed it? Also, can I find out what change was made?
We can't use 3rd party tools like Log Explorer (much as I want to), so this needs to be a purely SQL Server method.
Any help would be very much appreciated.
Owen
Owen West
Programmer,
Hunter Area Health Service
Why not put a trigger onto your database's sysobjects table and use it to log modifications in another table. Get the user's data from master.dbo.sysprocesses. This includes the host and NT username they're connecting from, as well as the SQL user id.
Graham
[This is assuming you can't just deny CREATE PROCEDURE permission to the suspect users, because everybody logs in as sa.]
May 20, 2002 at 5:53 pm
Actually, maybe the trigger should be on syscomments?
May 20, 2002 at 7:55 pm
I don't know that this is the best way to deal with it. Personally I would start slapping restrictions on DB access and not allow people to alter items without approval. Placing a trigger on the system tables can have detremental effects (if it can even be done but I have never tried and would be afraid to cause issues from this). If you can't lock down the DB you have issues larger than peopl changing things as if they have this kind of access with not control they can kill whatever you put in place to prevent them. Other than this Profiler Traces will be about your best bet, again thou performance hits will occurr. I thus go back to my best suggestion, take away their power and when they try to do it again you will hear complaints then you aim and shoot (the biggest baddest rubber band you can find) right at their heads.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 9:34 pm
Oh this makes me long for v6.5 in some ways.
First, Antares is right. Reread his post, twice, before you make any decision. then, here are a couple suggestions.
System table trigger - Not guarenteed to fire and not sure this is allowed in 2000. I thought they prevented this in v7, but have to check. Anyway, bad idea. Any upgrade, service pack, etc could wipe it out and it's a poor solution to an administrative problem.
Changes - I've written a little about this (in version control and auto gather info series) so I have some ideas if you can't use a third party.
1. Check the crdate everyday for all objects. Anything that changed in the last 24 hours that you did not change, stop everything and find out what happened. This will help with drop/creates and new items. It allows you to keep some control on your environment.
2. Alters get around this, but there is a field (I wrote about it in the Auto Gather Series) that is incremented by 16 every version change. this includes clustered index rebuilds, so it gets wacky, but store the results of all objecs in some table/db that only the DBA (you) can access. Then re-run the values everyday and compare to the stored values. If anything changed, stop immediately.
With both of these methods, you then need to go find someone and get them to admit a change. I used to check this everyday in a report and then ping the developers with a quick email or stopoff at their area. If no one admitted this by lunch, I'd go find their manager or the VP if needed and start raising hell. There is NO REASON for changes to be made to a live server without them being tested and the DBA being informed.
Last thing, log your changes. Every single thing you run on a production server should be logged somewhere so you can then be sure you did not cause the issue yourself. Even if you admin breaking things, at least you will know quickly that you did it and correct it.
Last last thing, use version control. No excuses not to do this.
HTH, Steve
Steve Jones
May 23, 2002 at 6:45 am
I've had the same issues. The only way I've resolved it is to deny all changes (structural, proceudres, etc.) to development and have it managed by a couple of administrators.
If the dev team make any changes to their SQL and break it, tough. I can roll back to the copy on the production server, or roll a copy out of source safe.
When they need to roll out to production, I can compare what has changed either with ExamDiff Pro (great little shareware proggy), SourceSafe or SQLCompare (highly recommended proggy this).
Changes are documented and confirmed, logged under source control and then scripted across.
This way I get a log of who has changed anything, what the old version was (if it breaks, it gets rolled back in production and the dev team are asked to sort it).
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
May 23, 2002 at 6:59 am
Steve,
Great post. Like the suggestion of monitoring create date in sysobjects. I have my security well locked down in all areas except with a couple of legacy customers / developers and it would be nice to have that monitoring in place.
Already working on the code. Thanks.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply