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.