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]

Pivot Query....

Working on roles for projects (roles are granted and denied based on
dates) and I need to display a summary (on the web) that lists the
roles down the left column, with the dates along the top, and the
numbers needed in the matrix that results from the rows and columns
created by roles/dates...
Hey, I totally need some help with this, I know its wrong..but I need
ALL of the dates, not just a few...
Basically I need to sum up the numberneeded by date and by
role...anyone out there please help me out on this?
--Code
select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
NumberNeeded
from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
pd.PrjDate as ProjectDate,
rfd.ProjectRoleID, rfd.NumberNeeded from
ut_extras_Projects_RolesForDate rfd
right outer join ut_extras_projectdates pd
on rfd.ProjectDateID = pd.PrjDateID) tmp
PIVOT
(
SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
)
--end code
Hi
Without seeing the sample data + an expected result it is really hard to
suggest something
<Tremmorkeep@.gmail.com> wrote in message
news:5b32f45d-9405-4844-b840-a32088a0c4a9@.d27g2000prf.googlegroups.com...
> Working on roles for projects (roles are granted and denied based on
> dates) and I need to display a summary (on the web) that lists the
> roles down the left column, with the dates along the top, and the
> numbers needed in the matrix that results from the rows and columns
> created by roles/dates...
>
> Hey, I totally need some help with this, I know its wrong..but I need
> ALL of the dates, not just a few...
> Basically I need to sum up the numberneeded by date and by
> role...anyone out there please help me out on this?
>
> --Code
> select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
> NumberNeeded
> from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
> pd.PrjDate as ProjectDate,
> rfd.ProjectRoleID, rfd.NumberNeeded from
> ut_extras_Projects_RolesForDate rfd
> right outer join ut_extras_projectdates pd
> on rfd.ProjectDateID = pd.PrjDateID) tmp
> PIVOT
> (
> SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
> )
> --end code

Pivot Query....

Working on roles for projects (roles are granted and denied based on
dates) and I need to display a summary (on the web) that lists the
roles down the left column, with the dates along the top, and the
numbers needed in the matrix that results from the rows and columns
created by roles/dates...
Hey, I totally need some help with this, I know its wrong..but I need
ALL of the dates, not just a few...
Basically I need to sum up the numberneeded by date and by
role...anyone out there please help me out on this?
--Code
select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
NumberNeeded
from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
pd.PrjDate as ProjectDate,
rfd.ProjectRoleID, rfd.NumberNeeded from
ut_extras_Projects_RolesForDate rfd
right outer join ut_extras_projectdates pd
on rfd.ProjectDateID = pd.PrjDateID) tmp
PIVOT
(
SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
)
--end codeHi
Without seeing the sample data + an expected result it is really hard to
suggest something
<Tremmorkeep@.gmail.com> wrote in message
news:5b32f45d-9405-4844-b840-a32088a0c4a9@.d27g2000prf.googlegroups.com...
> Working on roles for projects (roles are granted and denied based on
> dates) and I need to display a summary (on the web) that lists the
> roles down the left column, with the dates along the top, and the
> numbers needed in the matrix that results from the rows and columns
> created by roles/dates...
>
> Hey, I totally need some help with this, I know its wrong..but I need
> ALL of the dates, not just a few...
> Basically I need to sum up the numberneeded by date and by
> role...anyone out there please help me out on this?
>
> --Code
> select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
> NumberNeeded
> from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
> pd.PrjDate as ProjectDate,
> rfd.ProjectRoleID, rfd.NumberNeeded from
> ut_extras_Projects_RolesForDate rfd
> right outer join ut_extras_projectdates pd
> on rfd.ProjectDateID = pd.PrjDateID) tmp
> PIVOT
> (
> SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
> )
> --end code

Pivot Query....

Working on roles for projects (roles are granted and denied based on
dates) and I need to display a summary (on the web) that lists the
roles down the left column, with the dates along the top, and the
numbers needed in the matrix that results from the rows and columns
created by roles/dates...
Hey, I totally need some help with this, I know its wrong..but I need
ALL of the dates, not just a few...
Basically I need to sum up the numberneeded by date and by
role...anyone out there please help me out on this?
--Code
select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
NumberNeeded
from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
pd.PrjDate as ProjectDate,
rfd.ProjectRoleID, rfd.NumberNeeded from
ut_extras_Projects_RolesForDate rfd
right outer join ut_extras_projectdates pd
on rfd.ProjectDateID = pd.PrjDateID) tmp
PIVOT
(
SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
)
--end codeHi
Without seeing the sample data + an expected result it is really hard to
suggest something
<Tremmorkeep@.gmail.com> wrote in message
news:5b32f45d-9405-4844-b840-a32088a0c4a9@.d27g2000prf.googlegroups.com...
> Working on roles for projects (roles are granted and denied based on
> dates) and I need to display a summary (on the web) that lists the
> roles down the left column, with the dates along the top, and the
> numbers needed in the matrix that results from the rows and columns
> created by roles/dates...
>
> Hey, I totally need some help with this, I know its wrong..but I need
> ALL of the dates, not just a few...
> Basically I need to sum up the numberneeded by date and by
> role...anyone out there please help me out on this?
>
> --Code
> select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
> NumberNeeded
> from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
> pd.PrjDate as ProjectDate,
> rfd.ProjectRoleID, rfd.NumberNeeded from
> ut_extras_Projects_RolesForDate rfd
> right outer join ut_extras_projectdates pd
> on rfd.ProjectDateID = pd.PrjDateID) tmp
> PIVOT
> (
> SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
> )
> --end code