Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Different schemas / tables / procedures -> jump to dbo and back to custom schema Expand / Collapse
Author
Message
Posted Tuesday, January 20, 2009 5:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 20, 2009 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 20, 2009 6:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695
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
Posted Tuesday, January 20, 2009 6:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695
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
Posted Tuesday, January 20, 2009 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse