Remove System Stored Procedures in TempDB

Question

Good afternoon.

I've been tasked to remove all 'permanent stored procedures from the TempDB for security compliance.  My concern is the System Stored Procedures located there.  Can I safely remove them or will that cause havoc and headaches?

Thank you.

-Todd

Answer 1

Hi,

Those stored procedures are created and managed by SQL engine, IMHO you shouldn't alter or delete them.

Nevertheless, if you still want to tinker with them do it in a sandboxed environment (where your live servers won't be affected).

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

No It cant, because those required to perform or pull some useful information in-case if any issues(for ex- performance) occurs or required to pull the information.If needed you can modify either in th model or tempdb(if needed so).. but leave as it is.

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 3

There are no system stored procedures in TempDb.  The system stored procedures in the 'sys' schema are actually in the Resource Database.

Any permanent tables or stored procedures actually present in TempDb were put there by a user or an application.  You can (from SQL Server's POV) safely remove them.  Of course you might break an application.

eg

select * 
from tempdb.sys.objects
where type in ('U','P')
and name not like '#%'

One note, the ASP.NET Session Storage script will, by default, use permanent tables in TempDb.  You might not want to remove those.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

thanks David,

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 5

Perfect.  That's exactly what I needed.  Thank you.

-Todd