Bug in INFORMATION_SCHEMA.SCHEMATA?

This is SQL Server 2000 with SP3a.
When I do
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
I get only a subset of databases listed. I did check the permission to the
non-listed databases, and without knowing exactly what permission "chain"
the above view uses, the user definitely "has access" to the missing
databases. I tried both with an account with very limited permissions (SQL
Server login), "sa" (SQL Server login) and Integrated Security login from a
Windows account in the (Local) Administrators group on the SQL Server
machine. The login "BUILTIN\Administrators" has SA access, and has
permissions to all the databases.
If I connect as the same user to a different ("unlisted") database, the
current database shows up in the above query, and the one that I was
previously connected to (and therefore showed up) is gone from the results.
I also tried
SELECT HAS_DBACCESS('<unlisted db>')
and received 1 as a result (in QA, while connected as a limited user to
another database - also unlisted).
Most (or all?) of the "unlisted" databases have been created by a script run
in QA and/or with osql.exe (command line parameters). Is there something I
am missing? My script assignes specific privilleges to a role that I
create, as well as to the "public" role.
The assignment of privilleges are done per the following script excerpt:
if not exists (select * from master.dbo.syslogins where loginname =
N'SomeUser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'master', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid <
16382)
EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
GO
exec sp_addrolemember N'db_datareader', N'SomeUser'
GO
exec sp_addrolemember N'db_datawriter', N'SomeUser'
GO
exec sp_addrolemember N'my_app_role', N'SomeUser'
GO
(You may recognize this as (based on) output from EM Generate SQL Script...)
I know I can use master..sysdatabases, but I am really trying to follow MS'
advice not to access the system tables directly...
While I found some similar questions through Google, I couldn't find any
that addressed this as an actual issue - apparently the "don't have access"
was more or less accepted without much protest...
Help, please...
Tore.Is the 'auto close' database option turned on? Apparently, closed database
are not included in this view.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tore" <pointzero@.vbdreamteam.net> wrote in message
news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> This is SQL Server 2000 with SP3a.
> When I do
> SELECT CATALOG_NAME
> FROM INFORMATION_SCHEMA.SCHEMATA
> I get only a subset of databases listed. I did check the permission to
> the non-listed databases, and without knowing exactly what permission
> "chain" the above view uses, the user definitely "has access" to the
> missing databases. I tried both with an account with very limited
> permissions (SQL Server login), "sa" (SQL Server login) and Integrated
> Security login from a Windows account in the (Local) Administrators group
> on the SQL Server machine. The login "BUILTIN\Administrators" has SA
> access, and has permissions to all the databases.
> If I connect as the same user to a different ("unlisted") database, the
> current database shows up in the above query, and the one that I was
> previously connected to (and therefore showed up) is gone from the
> results.
> I also tried
> SELECT HAS_DBACCESS('<unlisted db>')
> and received 1 as a result (in QA, while connected as a limited user to
> another database - also unlisted).
> Most (or all?) of the "unlisted" databases have been created by a script
> run in QA and/or with osql.exe (command line parameters). Is there
> something I am missing? My script assignes specific privilleges to a role
> that I create, as well as to the "public" role.
> The assignment of privilleges are done per the following script excerpt:
> if not exists (select * from master.dbo.syslogins where loginname =
> N'SomeUser')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'master', @.loginlang = N'us_english'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
> master.dbo.syslanguages where name = @.loginlang) and @.loginlang <>
> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
> END
> GO
> if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid
> < 16382)
> EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datareader', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datawriter', N'SomeUser'
> GO
> exec sp_addrolemember N'my_app_role', N'SomeUser'
> GO
> (You may recognize this as (based on) output from EM Generate SQL
> Script...)
> I know I can use master..sysdatabases, but I am really trying to follow
> MS' advice not to access the system tables directly...
> While I found some similar questions through Google, I couldn't find any
> that addressed this as an actual issue - apparently the "don't have
> access" was more or less accepted without much protest...
> Help, please...
> Tore.
>|||Thanks,
Yes, autoclose is on (which is a reasonable option for these databases).
I guess it's back to the system tables... :-<
Regards,
Tore.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OoS3%23ftRFHA.580@.TK2MSFTNGP15.phx.gbl...
> Is the 'auto close' database option turned on? Apparently, closed
> database are not included in this view.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tore" <pointzero@.vbdreamteam.net> wrote in message
> news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
The root cause of this is that the DATABASEPROPERTY() and
DATABASEPROPERTYEX() functions return NULL in cases where the database is
off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
expression on one of these functions; it returns null, and the so the row
is not seen in the result set.
This "Returns NULL" behavior of those functions is documented in the SQL
2005 BOL.
In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
change in SQL 2005. It now returns all the schemas in the current catalog
(database) rather than all catalogs (databases) in the server. This
changes brings our implementation into compliance with the SQL standard.
Sadly, our SQL 2000 implementation of that view was in error. This
breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
will detect it for you.
Unforutnately, the SQL standard does not provide as way to discover all
catalogs (databases) in the server. From the standard's perspective, that
is an "implementation detail." I can tell you that it is OK to use
master.dbo.sysdatabases in your SQL2K application and it will upgrade to
SQL 2005. NB: We have modified the security rules around who can "see" rows
in catalog views in SQL 2005. In this particular case, we've introduced a
new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed to
PUBLIC by default. The net result is that your application should continue
to work after you upgrade to SQL 2005.
IN general, you are OK to use system tables as long as you STAY AWAY FROM
UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
guarantees to support them in SQL 2005.
So, for example, avoid things like:
* sysproperties - undocumented
* sysxlogins - undocumented
* sysdatabases.mode - internal only
* sysdatabases.reserved - reserved
The rule of thumb is this:
If the system table has a topic page devoted to it in the BOL, it is OK.
If the column is documented and it does not say "internal" or "reserved",
it is OK.
Regards,
Cliff Dibble
Program Manager, SQL Server Engine
MSFT
Message posted via http://www.webservertalk.com|||Thanks for the detailed response. I find your hint about how to decide what
to stay away from especially useful.
However, the way I read the SQL Standard definition of
INFORMATION_SCHEMA.SCHEMATA is (quote):
"Identify the schemata that are owned by a given user."
Inspection of the underlying query also indicates that it refers to the
catalog that contains the information schema.
Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA,
it seems that INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?)
would be a prime candidate... :->
Thanks and Regards,
Tore.
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
> Hi,
> The root cause of this is that the DATABASEPROPERTY() and
> DATABASEPROPERTYEX() functions return NULL in cases where the database is
> off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
> expression on one of these functions; it returns null, and the so the row
> is not seen in the result set.
> This "Returns NULL" behavior of those functions is documented in the SQL
> 2005 BOL.
> In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
> change in SQL 2005. It now returns all the schemas in the current catalog
> (database) rather than all catalogs (databases) in the server. This
> changes brings our implementation into compliance with the SQL standard.
> Sadly, our SQL 2000 implementation of that view was in error. This
> breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
> will detect it for you.
> Unforutnately, the SQL standard does not provide as way to discover all
> catalogs (databases) in the server. From the standard's perspective, that
> is an "implementation detail." I can tell you that it is OK to use
> master.dbo.sysdatabases in your SQL2K application and it will upgrade to
> SQL 2005. NB: We have modified the security rules around who can "see"
> rows
> in catalog views in SQL 2005. In this particular case, we've introduced a
> new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed
> to
> PUBLIC by default. The net result is that your application should
> continue
> to work after you upgrade to SQL 2005.
> IN general, you are OK to use system tables as long as you STAY AWAY FROM
> UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
> guarantees to support them in SQL 2005.
> So, for example, avoid things like:
> * sysproperties - undocumented
> * sysxlogins - undocumented
> * sysdatabases.mode - internal only
> * sysdatabases.reserved - reserved
> The rule of thumb is this:
> If the system table has a topic page devoted to it in the BOL, it is OK.
> If the column is documented and it does not say "internal" or "reserved",
> it is OK.
> Regards,
> Cliff Dibble
> Program Manager, SQL Server Engine
> MSFT
> --
> Message posted via http://www.webservertalk.com|||> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA, it seems that

> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a prime ca
ndidate... :->
But that would make it proprietary, just the same as sysdatabases and the fu
ture sys.databases
are...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tore" <pointzero at vbdreamteam dot net> wrote in message
news:Oo5FacUSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Thanks for the detailed response. I find your hint about how to decide wh
at to stay away from
> especially useful.
> However, the way I read the SQL Standard definition of INFORMATION_SCHEMA.
SCHEMATA is (quote):
> "Identify the schemata that are owned by a given user."
> Inspection of the underlying query also indicates that it refers to the ca
talog that contains the
> information schema.
> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA
, it seems that
> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a
prime candidate... :->
> Thanks and Regards,
> Tore.
>
> "Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in messa
ge
> news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
>|||Initially we were against adding anything new that was not explicit in the
standard. However, there does appear to be an escape clause in the
standard. See section 4.2, p.8 of Part 11.
"An SQL-implementation may define objects that are associated with
INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
implementation or any future version of ISO/IEC 9075 may also add columns
to tables that are defined in this Clause."
It's not entirely clear how we'd do that without risking some future
incompatibility with the standard. We'd probably have to define some MSFT-
specific naming convention.
In any case, we did not get around to adding I/S customizations in Yukon.
We will consider adding more extensions doing a fuller implementation of
the I/S views in the next product release. The I/S views encapsulate a lot
of JOINs required in the underlying catalog.
Regards,
Cliff Dibble
PM, SQL Engine
Message posted via http://www.webservertalk.com|||Just to close on the topic of system table compatibility in Yukon, here is
the complete list of previously documented columns that are no longer
compatible in Yukon, along with an explanation why. You should avoid
these, too.
view column_name value rationale
========================================
====================================
==
syscomments compressed 0 ctext is always uncompressed in Yukon.
sysdevices size 0 This has been deprecated since SQL2K
sysindexes statblob NULL Security reasons.
sysindexes keys NULL Severe perf hit. The format of the
binary
data was not documented.
sysobjects schema_ver 0 Due to architectural change, can not
guarantee strict monotonicity due to
round
off errors when converting/mapping from
8-byte datetime to 4-byte integer.
Use sys.objects.modify_date instead.
sysoledbusers rmtpassword NULL Security reasons.
sysremotelogins status 0 The format of the options was not
documented.
sysservers topologyy 0 The UI no longer uses this.
There are also a few incompatibilities in sysindexes (which are doc'ed in
the most recent SQL 2005 CTP builds):
a) No more indid=255 due to storage engine changes.
b) The page counters like "dpages" don't row-overflow.
c) The behavior of rowmodctr is not exactly the same due to storage engine
changes (we now carry per-column modification counters).
d) maxirow values are not identical due to storage engine changes.
e) keys column is NULL, as previously mentioned
f) The name column is NULL for heaps because we now have a unique index on
that column and we need to avoid possible name conflicts.
Finally - note that if you use NEW features in Yukon (e.g. partitioning or
any of the new user/schema DDL like CREATE USER), you will absolutely need
to migrate to the new catalog views. For example, sysindexes.root is NULL
for partitioned indexes because each partition has its own B-tree. Another
example:
select * from sysobjects where uid=1 and name = 'X'
might return >1 row due to user/schema separation (suppose dbo owned two
schemas S1 and S2, each of which contained a table named X).
Regards,
Cliff Dibble
PM SQL Engine
Message posted via http://www.webservertalk.com|||That's a good list, Clifford. Any plans to document it? (Or did I miss it in
April CTP BOL?)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:3d4abaedc650452998ebec042f092996@.SQ
webservertalk.com...
> Just to close on the topic of system table compatibility in Yukon, here is
> the complete list of previously documented columns that are no longer
> compatible in Yukon, along with an explanation why. You should avoid
> these, too.
> view column_name value rationale
> ========================================
==================================
====
> syscomments compressed 0 ctext is always uncompressed in Yukon.
> sysdevices size 0 This has been deprecated since SQL2K
> sysindexes statblob NULL Security reasons.
> sysindexes keys NULL Severe perf hit. The format of the
> binary
> data was not documented.
> sysobjects schema_ver 0 Due to architectural change, can not
> guarantee strict monotonicity due to
> round
> off errors when converting/mapping from
> 8-byte datetime to 4-byte integer.
> Use sys.objects.modify_date instead.
> sysoledbusers rmtpassword NULL Security reasons.
> sysremotelogins status 0 The format of the options was not
> documented.
> sysservers topologyy 0 The UI no longer uses this.
> There are also a few incompatibilities in sysindexes (which are doc'ed in
> the most recent SQL 2005 CTP builds):
> a) No more indid=255 due to storage engine changes.
> b) The page counters like "dpages" don't row-overflow.
> c) The behavior of rowmodctr is not exactly the same due to storage engine
> changes (we now carry per-column modification counters).
> d) maxirow values are not identical due to storage engine changes.
> e) keys column is NULL, as previously mentioned
> f) The name column is NULL for heaps because we now have a unique index on
> that column and we need to avoid possible name conflicts.
>
> Finally - note that if you use NEW features in Yukon (e.g. partitioning or
> any of the new user/schema DDL like CREATE USER), you will absolutely need
> to migrate to the new catalog views. For example, sysindexes.root is NUL
L
> for partitioned indexes because each partition has its own B-tree. Anothe
r
> example:
> select * from sysobjects where uid=1 and name = 'X'
> might return >1 row due to user/schema separation (suppose dbo owned two
> schemas S1 and S2, each of which contained a table named X).
> Regards,
> Cliff Dibble
> PM SQL Engine
> --
> Message posted via http://www.webservertalk.com|||> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
My .02: Don't.
I think can see the temptation: "By just adding this column, the view will b
e usable in a lot more
scenarios."
To me, the purpose of these views are that you know you are within the stand
ard. If I would need
something outside these views, I prefer diving into the catalog views or sys
tem tables, as I would
still need a re-write in the unlikely case I need to port the app. Also, the
catalog views have a
much better design than the old style system tables, so ease of use (info sc
hema vs. system tables)
isn't an argument either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:8ee5d4d5a35947ec98ee43006ed152cd@.SQ
webservertalk.com...
> Initially we were against adding anything new that was not explicit in the
> standard. However, there does appear to be an escape clause in the
> standard. See section 4.2, p.8 of Part 11.
> "An SQL-implementation may define objects that are associated with
> INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
> implementation or any future version of ISO/IEC 9075 may also add columns
> to tables that are defined in this Clause."
> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
> In any case, we did not get around to adding I/S customizations in Yukon.
> We will consider adding more extensions doing a fuller implementation of
> the I/S views in the next product release. The I/S views encapsulate a lo
t
> of JOINs required in the underlying catalog.
> Regards,
> Cliff Dibble
> PM, SQL Engine
> --
> Message posted via http://www.webservertalk.com