Yup, and from a security perspective this makes a lot of sense. Consider the following scenario:

Instead of the current behavior, SQL Server searches in the local database first for any stored procedure, even ones that are normally system stored procedures.

I have the ability to create a stored procedure in a user database. I decide to name it sp_password. My stored procedure does the following:

(1) it emails me the username and password

(2) it then calls sp_password from the master database and performs the password update just fine.

See the problem?

Microsoft recently released a security bulletin that most of have said, "Tell me something I didn't know" which points out that if you let something drop an executible in C:\ (root), and it happens to be the same name as a system executible say like ping or any of the others, the one in root will be found and executed first if you try to run it by Start | Run. The reason, of course, is because while C:\ is not typically in the search path, for operations like Start | Run it can be. Oops. Here's the article:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/MS02-064.asp

So the simple solution is not to use sp_ in user databases and the whole issue is avoided. As it is now, a security issue is mitigated with SQL Server's default behavior. This shouldn't change.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1