January 20, 2009 at 5:48 am
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
January 20, 2009 at 5:51 am
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');
January 20, 2009 at 6:04 am
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/
January 20, 2009 at 6:05 am
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/
January 20, 2009 at 6:13 am
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