Different schemas / tables / procedures -> jump to dbo and back to custom schema

  • Hello,

    I'm having a default schema (dbo) and a custom schema (custom).

    I'm having the same table in both schemas (testtable)

    I've created a parent procedure in dbo upTestSub

    And I've created in both schemas a child procedure upSub (which is a select * from testtable).

    If I login with a user with default_schema = custom, then when I'm calling upTestSub it shows me the

    records from custom.testtable

    If i login with a user with default_schema = dbo, then it shows me dbo.testtable.

    -> This is fine and expected.

    But when I'm calling another procedure upTest (which is a select from testtable too), it always takes the dbo.testtable, no matter what the default_schema is for the user. I would expect the ownerchip chain to be broken

    Can anyone help me?

    I've attached the sample database.

    Tx

    Geoffrey

  • the attachment upload doesn't work for the moment.

    here the script:

    USE [TestSchemas]

    GO

    /****** Object: User [custom] Script Date: 01/20/2009 13:57:42 ******/

    CREATE USER [custom] FOR LOGIN [custom] WITH DEFAULT_SCHEMA=[custom]

    GO

    /****** Object: Schema [custom] Script Date: 01/20/2009 13:57:42 ******/

    CREATE SCHEMA [custom] AUTHORIZATION [custom]

    GO

    /****** Object: Table [dbo].[TestTable] Script Date: 01/20/2009 13:57:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[TestTable](

    [TestCol] [nchar](10) NULL

    ) ON [PRIMARY]

    GO

    /****** Object: Table [custom].[TestTable] Script Date: 01/20/2009 13:57:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [custom].[TestTable](

    [TestCol] [nchar](10) NULL

    ) ON [PRIMARY]

    GO

    /****** Object: StoredProcedure [dbo].[upTest] Script Date: 01/20/2009 13:57:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create procedure [dbo].[upTest]

    as

    begin

    select * from TestTable

    end

    GO

    /****** Object: StoredProcedure [dbo].[upSub] Script Date: 01/20/2009 13:57:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE procedure [dbo].[upSub]

    as

    begin

    select * from testtable

    end

    GO

    /****** Object: StoredProcedure [custom].[upSub] Script Date: 01/20/2009 13:57:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE procedure [custom].[upSub]

    as

    begin

    select * from testtable

    end

    GO

    /****** Object: StoredProcedure [dbo].[upTestSub] Script Date: 01/20/2009 13:57:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create procedure [dbo].[upTestSub]

    as

    begin

    exec upSub

    end

    GO

    insert into dbo.testtable values ('dbo');

    insert into custom.testtable values ('dbo');

  • I would expect to have to always fully qualify objects, I'm not a great lover of schemas for schemas sake, I've seen a number of implementations of schemas just because they are available without any real thought of why, how and will it give problems. In your proc you must be qualifying the table ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • sorry didn't read all the scripts correctly - you illustrate correctly why calls to objects should always be fully qualified.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Hi. I agree not to use them for schemas sake use, but we explicitly want to have this behavior, except it doesn't work for tables. I would like to have some explanation of somebody, so I can understand why it works for procs, but not for tables.

    tx

    Geoffrey

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply