I accidentally deleted the system stored procedure sp_grantdbaccess. I've added it back into the master database, and the owner is correctly listed as DBO. However, the owner column value in Enterprise Manager shows up as user when I look at the list of procedures in Enterprise Manager. I'm worried that something won't work properly if SQL Server thinks the procedure is a user procedure rather than a system procedure. How can I get Enterprise Manager to list sp_grantdbaccess as a system procedure again?
First, the owner column value being user won't affect the procedure's ability to work as it's intended for managing account security in SQL Server. However, you can use the sp_MS_marksystemobject procedure to correctly display the procedure as a system object:
EXEC sp_MS_marksystemobject 'sp_grantdbaccess'
The sp_MS_marksystemobject procedure sets a bit in the status column of the master..sysobjects table, which various SQL Server tools (such as Enterprise Manager) use to determine if the object is a system object. Interestingly, sp_MS_marksystemobject is one of the few procedures that doesn't show up in Enterprise Manager's list of stored procedures, even when Enterprise Manager is configured to show system objects. However, you can view the procedure's text by using
sp_helptext 'sp_MS_marksystemobject'
