Protect Your T-SQL Objects from Deletion


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

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.