Drop and re-create System Stored Procedures in SQL Server 2000

Today in one of the SQL communities, I came across a question. The scenario was something like this.

One of the audit report stated that someone had dropped an re-created a System Stored Procedure on an instance running SQL Server 2000. However, the person whose name was listed in the audit report denied doing it. When checked the creation date of the System Stored Procedure matched that of the one listed in the Audit report. The question was “Is it possible to drop and re-create a system stored procedure in SQL Server 2000. Even if it is possible how to make it System Stored Procedure?“.

Unfortunately answer to both of the above questions is a big Yes. In order to confirm this behavior let me use this example where I try to drop and re-create sp_addlogin system stored procedure. Since SQL Server Enterprise Manager does not list System and User stored procedures seperately, I am using SSMS in this example.

The sp_addlogin stored procedure is listed under master –> system stored procedures.

Now I will copy the content of the stored procedure using this query.

EXEC sp_helptext 'sp_addlogin'

Next step is to drop the stored procedure. Before that I need to ensure that SQL Server allows modifications to the System Objects. It is disabled by default.

sp_configure 'allow updates',1

Without this setting, SQL Server will not allow any changes to the System objects and throws this error.

Now all set to drop the stored procedure.

DROP PROCEDURE sp_addlogin

and yes, the command executed successfully. Now I will execute the content of the stored procedure of which I had taken a copy earlier. The command executed successfully, but it is not getting listed under System Stored Procedures, instead SQL Server treats it as a User Stored Procedure.

Why this behavior? A quick glance at the sysobjects table reveals it.

The status column has a negative value for the “actual” system stored procedure. Since SQL Server 2000 allows changes to System Tables why not change this one as well ! But which value to select? Let me get a list of status ids for all the system stored procedures in master database.

Since 5 is my favorite number, I will choose the one ending with 5 😉

UPDATE sysobjects SET STATUS ='-1610612735' WHERE name='sp_addlogin'

Command executed successfully and sp_addlogin is back in to the shell.

Statutory Warning: Modifying the System Objects may not go well with SQL Server’s health. It may also affect the DBA’s health at the worst possible time.

In order to keep the DBA away from unwanted surprises, the modification to system objects is not allowed on SQL Server 2005 and above !

2 thoughts on “Drop and re-create System Stored Procedures in SQL Server 2000

  1. Pingback: Find Startup procedures in an instance | SQLServerPedia

Comments are closed.