Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Block the DBA? Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2003 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, February 22, 2014 2:31 AM
Points: 64, Visits: 8
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
http://vyaskn.tripod.com/



HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
Post #52446
Posted Wednesday, January 29, 2003 7:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #52447
Posted Thursday, January 30, 2003 11:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 30, 2004 8:52 AM
Points: 253, Visits: 1
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





Post #52448
Posted Thursday, September 25, 2003 12:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 553, Visits: 22
is it possible to avoid dropping TRIGGERS ?
--YOUSEF




Post #52449
Posted Thursday, September 25, 2003 7:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #52450
Posted Thursday, September 25, 2003 1:04 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 553, Visits: 22
quote:

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

thanks ,but I want to restrict other DBA from performing Dropp triggers against the table.any idea like this article is needed.




Post #52451
Posted Wednesday, January 28, 2004 2:31 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2008 2:33 AM
Points: 31, Visits: 3

I have read this article before, and while it provides useful information, it is in poor taste especially when IT departments all over the world are struggling to keep systems running. Giving ideas to 'rogue' database developers and DBAs only increases the complexities of disaster recovery. When we are all struggling to keep new virii at bay, do we really need to add more sorrow on our desks?



--
Post #97592
Posted Wednesday, January 28, 2004 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:06 AM
Points: 262, Visits: 128

Is there really such a thing as an "unknowledgable DBA"?

The techniques explained while "illegal" could be put to good use for a small IT shop.  I don't see this as being practical for large IT shops. 

Also, these techniques are also unnecessary if the "real" DBA's have control of a system and know how to grant proper access to the database.  In the IT shop I work for, developers and "departmental" DBA's have to justify their security requirements to the database.  We don't hand out sysadmin privs unless there is no way around it.  Of course this is only possible, if the DBA's are given the authority to make these decisions.

Rusty




Post #97651
Posted Friday, January 30, 2004 6:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 305, Visits: 632

Echoing some of the previous post, very interesting, very dangerous.  Thanks for including the disclaimer at the end however, I pity the poor well meaning DBA who tries some of your hints as he is reading the article.

I seems to me that as professional DBAs it is our job to protect the database and not give any inexperienced person a weapon of mass destruction.  But then that's how some learn.

Again, some interesting thinking.  It's always fun to think outside the box but remember the following:  It's not that we shoot our selves in the foot so often that's amazing, it's amazing how fast we can reload.




Post #98098
Posted Tuesday, July 10, 2007 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 17, 2008 2:14 PM
Points: 2, Visits: 11
First of all, thank you for getting me out of a serious bind!

Supported or not, when the choice is between losing your job because you can't track harmful changes to sysusers or losing it because you enabled C2 auditing or tried using the profiler and tripled the server count, I'll choose changing something MS fouled up or just plain forgot, any day.

If it breaks something - guess what: I'll remove it. I might even have to go get a backup tape.

The holy system stored procs and MSFT's undocumented practices do a good enough job of tearing up the system tables anyway, why shouldn't we get in on the fun? We might even prevent them from breaking stuff.

It's not like there aren't a couple of dozen third party application categories dedicated to following them around and cleaning up their messes.

And I have a slight problem with their defining objects in MY databases as THEIRS. Master, MSDB, Distribution, and the like are THEIR databases. The ones I create are MINE.

Again, many thanks, and if something breaks, I've never even heard of you. I was never here, nobody saw me, I've got an alibi; that's my story and I'm sticking to it!
Post #380466
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse