January 25, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 26, 2003 at 6:31 pm
While its often fun to think about how you might block a problem DBA from screwing up a database, the methods presented in this article won't do much practical to address the issue. The solutions presented here could very well end up doing more harm than good. Microsoft specifically states that modifications to system tables are dangerous and should be avoided. Putting a trigger on a system table could have consequences that were not expected or intended.
I realize that at the end of the article you state that the techniques presented should not be used on a production database and hold little practical value; however, the lead-in to the article does not present the things in this light. While learning the structure of the system tables is useful, this article does not demonstrate any practical use of some potentially useful information. To me, it is dangerous and irresponsible to present, even momentarily, these techniques as a plausible solution to a very real problem.
Chris Cubley, MCSD
Chris Cubley
Chris Cubley
www.queryplan.com
January 26, 2003 at 9:36 pm
As far as making a table read-only, one of the things you can do is create a separate filegroup and build that table in the filegroup. Then use the ALTER DATABASE command and set the filegroup to read-only.
The one catch to this is that you may have to undo the read-only status prior to applying a service pack. Remove read-only, apply the service pack, and then reapply the read-only status would be the procedure there.
As far as preventing tables from being dropped or users from being added, I think this goes back to trust. Just as you have to trust whoever is holding domain admin rights (or enterprise admin rights for that matter), you are going to have to trust your senior DBAs. Junior DBAs can have reduced permissions, but you'd expect your senior DBAs not to go making changes without careful planning.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 27, 2003 at 3:26 am
Great article. Especialy the conclusion, wich IMO should be put en a red colour and an extra bold font.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 27, 2003 at 3:27 am
I know you said it was wortst practice in your article, but the icy hand of DBA's past gripped my heart when I read about altering system tables.
I am always nervous of changing catalogues, be they system or application catalogues unless I have a guarantee from the vendor that such changes can do no harm.
You would have to have throrough documentation on what you had done and a role back procedure so that all the changes could be dropped prior to the application of a service pack then reinstigated after the service pack.
The problem with altering system tables is that there is no guarantee that the changes will be applicable after an update i.e. MS change their methodology so that XTYPE is no longer used to validate system tables.
When it comes to enforcing best practice I prefer the approach of "Do it right and I'll buy you a pint, do stupid things (more than once) and its the big stick with the nails in it for you my boy"!
January 27, 2003 at 4:07 am
Also, with triggers there are no guarantees they will fire on system tables and that they might not cause your server heartburn so to speak within system transactions. Good concepts and I have played with the system tables myself on test servers but they always end up getting you in trouble down the line. Or what works one time may cause issues the next.
January 27, 2003 at 4:40 am
Interesting stuff. Im not opposed to someone using system tables if they know what they are doing. The downside I see is not that something might break (hey, you changed it!) but that it may get undone by a service pack (will you remember to check and reapply your change) and that it you're doing something not expected/easily visible for the next dba to come along.
I don't have a jr dba - idea kinda gives me chills! I think when you get to the point that you don't trust them, time for them to go. Note that I make the distinction between trust and worrying that they may make a mistake. Mistakes will happen, especially if you're learning. I think the thing to do is teach them to never do things without a recovery plan. Never never never. Maybe invest in LogExplorer too!
Andy
January 27, 2003 at 8:14 am
I knew I was likely to get various negative comments to this article. It isn't inteneded to be as serious as the others I have published. Please forgive me for not making my warning at the beginning of the article as strong as the warning at the end of the article. My advice is to use this article as a learning experience and to not use these techniques in a production environment until you have exausted all other possible solutions (this should mean never, if you think you have exhausted all other possible solutions then you probably haven't tried hard enough).
For those who have read some of my posts, you know I have no problem with modifying system tables in a development environment while exploring possible solutions and I have reaped the problems associated with doing things wrong.
The only change to system tables I have made in a production environment is deleting a user from sysusers when the ID is not matched and I don't think I've done that recently since I discovered a system SP that will fix the login.
And since you mentioned the unreliablility of triggers on system tables. The one described in the article worked reliably for me. I put an auditing trigger on sysprocesses in the master database. The trigger worked on other system tables but it didn't fire while on sysprocesses. It was there though, and even stayed after I shut down the SQL Server service and restarted it. I thought that when I switched xtype of sysprocesses from S to U that I might crash the server and even break it so that I would have to rebuild the server. That didn't happen, but the trigger on that system table would not fire. Please, don't try this on anything but a server you are willing to rebuild.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 28, 2003 at 10:31 am
Hi
Some very interesting thinking. I can thinking of a variety of senarios where some of these would apply but as the only dba, i can trust myself 😉
Cheers
Chris K
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 29, 2003 at 1:56 am
Actually your article highlights a need that isn't satisfied via legitemate means i.e. triggers on system tables.
Role on YUKON.
January 29, 2003 at 7:00 am
While you bring up some interesting points, a not-so-experienced DBA might be quick to implement these suggestions, without reading the 'conclusion' section.
Eventually this type of techniques end up doing more harm than good, as they are not supported by Microsoft, and these changes interfere with the SQL Server's functionality. Results can be unpredictable.
If you don't trust your DBA, you should keep them away from critical servers by restricting their access at the Windows and server level.
HTH,
Vyas
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
January 29, 2003 at 7:37 am
Yes, I think triggers on system tables can be useful in some cases. At the very least Microsoft could provide some triggers in an inactive form that could be activated should a DBA want them. The reason I tried to put a trigger on sysprocesses is because I developed a way to detect deadlocks and save the query of both processes involved in the deadlock. This would eliminate the need to review profiler data and to monitor for a certain period of time until a deadlock appeared.
Even though I am unlikely to use these techniques, I do believe that used correctly and with caution they would not cause an adverse effect on a SQL Server. Take, for example the first technique described. SQL Server sets this flag every time you set up replication, so the technique is not doing something that should never be done it is simply doing it without setting up replication. If you try to drop a replicated table you will get the same error as when you use the technique I described in example 1.
Also, these techniques can be used to avoid accidents. I think we have all made mistakes on production SQL Servers at one time or another.
The placing of triggers on system tables should only be done as experiments since this is something that SQL Server does not do naturally under any conditions and so they will be very risky at this point. Who knows, if I'm lucky someone from Microsoft will read this article and consider its implications and allow those who want triggers on system triggers to put them there.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 30, 2003 at 11:11 am
Ladies and Gentlemen,
Please do not be so serious. This article is entertaining - that's it. If a junior (or "SENIOR") DBA should take it seriously, the worst thing that could happen is that a production server gets burned to the ground. Not a big deal. Not as big as the Great Flood - for sure.
There is one way of teaching: you show your students the "worst practice" and "prohibited areas", how to go aroubd there, then you teach them not to be kiddish and irresponsible. After all, we are free people, right? (AKA, "guns do not shoot by themselves"...)
Michael
September 25, 2003 at 12:38 am
is it possible to avoid dropping TRIGGERS ?
--YOUSEF
September 25, 2003 at 7:20 am
You can use the ALTER TABLE command to disable and enable triggers. Also you can use sp_trigger created by Rodrigo G. Acosta. Its in the SQLServerCentral scripts area at: http://www.sqlservercentral.com/scripts/contributions/484.asp
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply