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
See Associated Figure
End of Article
