| Downloads |
|---|
| 95775.zip |
DBAs often create T-SQL stored objects (e.g., stored procedures, functions) in system databases to help with backup, reindexing, analyzing, and other database administration and maintenance tasks. Such objects usually reside in the master database. Because these objects are owned by the DBO user, they're created and marked as user objects. Hence, they're prone to deletions by other people who don't know that the objects are being used by DBAs.
I created a stored procedure, sp_markAllMasterDBasSystem, that can help prevent the deletion of these objects. It marks all nonsystem objects (i.e., objects marked as "non MS Shipped
Objects") as system objects. As Listing 1 shows, sp_markAllMasterDBasSystem uses the undocumented stored procedure, sp_MS_marksystemObject, for this job.
I tested sp_markAllMasterDBasSystem on SQL Server 2005 Developer Edition SP1 and SQL Server 2000 Enterprise Edition SP1. You need to compile this stored procedure in each system database.
If you create T-SQL objects to help with your database administration and maintenance tasks, give sp_markAllMasterDBasSystem a try. It will protect the objects you worked so hard to create.
—Eli Leiba
