sys.objects vs. sysobjects in master..sp_ stored procs

create procedure sp_GetObjects
as
select * from sys.objects

as noted before -- if it runs in a database context ( db..sp_GetObjects ) -- will still return all objects from master database.
alter procedure sp_GetObjects
as
declare @cmd nvarchar(1000)
select @cmd = 'select * from sys.objects'
exec sp_executesql @cmd

However running this proc in a database context will return correct results (objects from that database)..

At this point I have to assume it's a bug, and the correct catalog views will be accessed from master..sp_ procs at release time. (unless of course someone from sql server team disagrees...)
edit: @@version output: Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

PugV at 2007-9-9 >

This change in behavior is by-design.

If you substitute a user table

create procedure sp_GetObjects as
select * from some_user_table
go

you will see that it always binds relative to master. This is the normal TSQL behavior, and the new catalog views like sys.objects follow this model.

As Andrew wrote, the ability of some (not all) system tables like sysobjects, sysusers, etc. to bind and compile in the calling database context when called from stored procs named 'sp_%' in master.dbo is maintained for backwards compatibility only.

Today there is no supported way to create “database agnostic stored procedures”. However, we know customers are interested to have this feature, and we will consider it for the next product release.

Regards,
Clifford Dibble

PS. From microsoft.public.sqlserver.server newsgroup …

The best policy is to avoid using an "sp_" prefix because you run the risk of
a name clash when you upgrade to Yukon. As an alternative, you can use an
explicitly qualified 2-part name like "dbo.sp_whatever" to avoid ambiguity.
A side benefit of the 2-part name is that the run-time TSQL name resolver has
less work to do and so your code will execute just a *wee* bit faster.

In Yukon, all of the system objects like sp_help and sp_addtype are
logically contained in a new "sys" schema. This "sys" schema appears in
*every* database context, including master. Physcially, the system objects
that we ship are located in an entirely separate database called
mssqlsystemresource.mdf. The reason we relocated them OUT of master and INTO
mssqlsystemresource.mdf is so we can do faster upgrades. Instead of
DROP-ing and re-CREAT-ing 1000+ stored procedures, upgrade is now just a file
copy. Moreover, we can easily rollback an upgrade by restoring the original
mssqlsystemresource.mdf file.

In any case, all those 1000+ system stored procedues show up as objects in
the "sys" schema of every database.

When we are given an unqualified name reference sp_X in Yukon, we try to
resolve sp_X to an object by searching schemas in this order: sys, user's
default schema, dbo.

Notice that the "sys" schema is first in the search order. That means sp_X
will bind to the object in the "sys" schema before it binds to the object if
your own schema. That means any Microsoft object named sp_X will bind
tighter than a user object named sp_X. In essence, we have hijacked part of
the global namespace for a subset of the identifiers beginning with "sp_".
The reason we did this was to avoid "spoofing attacks." We don't *want* to
bind to a potentially malicous user copy of sp_X.

Similarly for fn_ and xp_ prefixes.

In the next product release, we may introduce a configurable schema search
path.

In summary, best practice is to

1) Explicitly qualify references with 2p names, and/or
2) Avoid using sp_, fn_, xp_ prefixes for user objects if you use
unqualified name references.

CliffordDibble at 2007-9-9 >

Hi,

I was not being sarcastic when I quoted “database agnostic stored procedures”

Internally, we have been calling them "write once run anywhere" (WORAs).

Just to be clear, this will not make it into the Yukon release. We are looking at Yukon+1.

Tell me more about about your scenarios. I will copy the text into our internal change tracking system. The more specific customers we have asking for it, the higher the liklihood it will get approved for the next release.

In Yukon we have introduced something called a SYNONYM. This is not a replacement, but I thought I'd mention it anyway. You can create a synonym in database1 that references a procedure in a different database.

USE thisdatabase
GO
CREATE SYNONYM p FOR otherdatabase.otherschema.otherproc
GO
EXEC P
GO

But a SYNONYM is just that. It doesn't change the name resolution or binding behavior, so it doesn't get the WORA behavior you want.

Thanks

CliffordDibble at 2007-9-9 >

Exactly so.

I have added your "correct permissions slip through the cracks" scenario to DCR #120913 "Customers want an official way to get library-style "write once, run-anywhere" (WORA) behavior (library of their own TSQL objects)"

Many thanks.

Clifford Dibble

CliffordDibble at 2007-9-9 >

Clifford Dibble wrote:

BTW - In SQL 2005 you can GRANT/DENY/REVOKE at different levels of scope. This can be an administrative convenience.

Excellent tip.
thanks!

PugV at 2007-9-9 >