PK columns dont show up in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Having a database user 'myuser' beeing a member of the roles 'public'
and 'db_owner' I created the test table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[myuser].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [myuser].[TEST]
GO
CREATE TABLE [myuser].[TEST] (
[TEST_ID] [varchar] (2) NOT NULL ,
[DESCRIPTION] [varchar] (60) NOT NULL ,
CONSTRAINT [TEST_PK] PRIMARY KEY CLUSTERED
(
[TEST_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
However, the primary key constraint 'TEST_PK' does not show up in the
view
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
only foreign keys (from other tables) show up.
Is this a security issue?
Using SQL Server 2000 Dev Edition SP3a on Win XP Prof.
Thank you in advance for your assistance,
SRSoenke,
This happens when a user tries to get schema information from tables
that they don't own. If you login as myuser it works fine. Can you
create the table as dbo.[TEST]? If you do this then it should work
without issue.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Soenke Richardsen wrote:
> Having a database user 'myuser' beeing a member of the roles 'public'
> and 'db_owner' I created the test table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[myuser].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable
') =
> 1)
> drop table [myuser].[TEST]
> GO
> CREATE TABLE [myuser].[TEST] (
> [TEST_ID] [varchar] (2) NOT NULL ,
> [DESCRIPTION] [varchar] (60) NOT NULL ,
> CONSTRAINT [TEST_PK] PRIMARY KEY CLUSTERED
> (
> [TEST_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> However, the primary key constraint 'TEST_PK' does not show up in the
> view
> select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> only foreign keys (from other tables) show up.
> Is this a security issue?
> Using SQL Server 2000 Dev Edition SP3a on Win XP Prof.
> Thank you in advance for your assistance,
> SR|||Hi Mark,
during the last week I tried several times to reply to your message
using google groups, but always got a message like:
"Unable to retrieve message OQ0Dm2T$EHA.3180@.TK2MSFTNGP10.phx.gbl"
Now I found the new beta groups, and they seem to work better...
Anyway, your posting helped me, thanks!
Soenke
Mark Allison wrote:[vbcol=seagreen]
> Soenke,
> This happens when a user tries to get schema information from tables
> that they don't own. If you login as myuser it works fine. Can you
> create the table as dbo.[TEST]? If you do this then it should work
> without issue.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Soenke Richardsen wrote:
'public'[vbcol=seagreen]
N'IsUserTable') =[vbcol=seagreen]
the[vbcol=seagreen]