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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Different schemas / tables / procedures ->...

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

Rate Topic

Display Mode

Topic Options

Author

Message

geoffrey.crombez-983463

Posted Tuesday, January 20, 2009 5:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 30, 2010 2:56 PM
Points: 8, Visits: 22

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

Post #639852

geoffrey.crombez-983463

Posted Tuesday, January 20, 2009 5:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 30, 2010 2:56 PM
Points: 8, Visits: 22

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');

Post #639857

colin.Leversuch-Roberts

Posted Tuesday, January 20, 2009 6:04 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

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 ?

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #639868

colin.Leversuch-Roberts

Posted Tuesday, January 20, 2009 6:05 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

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

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #639869

geoffrey.crombez-983463

Posted Tuesday, January 20, 2009 6:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 30, 2010 2:56 PM
Points: 8, Visits: 22

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

Post #639875

« Prev Topic | Next Topic »

Permissions