Re: PK columns dont show up in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

From: Mark Allison (mark_at_no.tinned.meat.mvps.org)
Date: 01/18/05

  • Next message: Sophie Guo [MSFT]: "Re: Slow performance with windows authentication" 
    Date: Tue, 18 Jan 2005 09:10:55 +0000

    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:
> 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
  • Next message: Sophie Guo [MSFT]: "Re: Slow performance with windows authentication"

    Relevant Pages

    • Re: SQL Server User
      ... Backup\Restore is a database level operation, but you need a valid Login to log in to your SQL Server and Logins are server level principals. ... you need a valid Login which is mapped to your User object in your database so that your user can log in to the SQL Server instance using that Login and Database User. ... now I am not able to access database with myuser in sql server where I ...
      (microsoft.public.sqlserver.connect)
    • Re: who is the owner..
      ... GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.tablename TO myuser ... SQL Server MVP ... > 1> if using SQL Authentication then instead of using the sa login to ... > 3> that the owner of the database is the user who created it. ...
      (microsoft.public.sqlserver.msde)
    • SQL Server User
      ... I restored the database in another sql server, version is same for both sql ... now I am not able to access database with myuser in sql server where I ... myuser & myrole also exist ...
      (microsoft.public.sqlserver.connect)
    • SQL Server User
      ... I restored the database in another sql server, version is same for both sql ... now I am not able to access database with myuser in sql server where I ... myuser & myrole also exist ...
      (microsoft.public.sqlserver.connect)
    • PK columns dont show up in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
      ... Having a database user 'myuser' beeing a member of the roles 'public' ... the primary key constraint 'TEST_PK' does not show up in the ...
      (microsoft.public.sqlserver.security)