Thu 19 Aug 2010
Sometimes, you want to enhance or change system stored procedures to add functionality like security related code. This is not supported and might blow up in your face so all the standard caveats apply. If it blows in your face, tough luck!
SQL2000 is pretty straight forward and you can find plenty of places on the web that will help you with the details.
Using SQL2005 and SQL2008 the process is a bit more complicated:
1. Stop SQL Server service.
2. On command prompt run the following command to run the server in single user mode – be sure to replace the MSSQLSERVER with the actual instance name
C:\>”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe” -sMSSQLSERVER -m
3. Connect to SQL server with SSMS as ADMIN:HOSTNAME
4. Run the following to change the database to the system resource database: “USE mssqlsystemresource”
5. Run the following to change the database to read-write – “alter database mssqlsystemresource set read_write”
6. Run the following to load the text of the procedure – exec sp_helptext ‘stored procedure name you want to change’
7. Alter the procedure and add the relevant code – “alter procedure …”
8. Return the system resource database to read-only – “alter database mssqlsystemresource set read_only”
9. Shutdown the instance – “shutdown”
10. Start the instance using the service as usual
And, that is it. A somewhat complex process to change a system stored procedure.