Schema use in SQL2005

Certainly...Se my Comments below in bold red....

The problem is how the EXEC statement resolves to the default schema. I'd like to know if this is a bug or expected behaviour or can it be resolved

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [myschema.[dnn_Forum_StatisticsGet] /*SCHEMA SPECIFIED is DEFAULT FOR THE USER*/

(

@ModuleID int,

@UpdateWindow int = 12,

@TabId int

DECLARE @LastUpdate datetime

DECLARE @DateWindow datetime

SET @LastUpdate = ISNULL((SELECT MAX(CreatedDate) FROM dnn_Forum_Statistics_Site), '1/1/1797')

SET @DateWindow = DATEADD(hh, -@UpdateWindow, GetDate())

if (@LastUpdate < @DateWindow)

BEGIN

EXEC dnn_Forum_AA_StatisticsSiteUpdate 0, 0, @ModuleID, @TabId /*THIS COMMAND FAILS AS SCHEMA NOT SPECIFIED - SQL2000 this would resolve to the default owner*/

END

SELECT TOP 1

S.*,

MostReadSubject = (SELECT Subject FROM dnn_Forum_Posts (nolock) WHERE PostID = S.MostReadThreadID),

MostViewsSubject = (SELECT Subject FROM dnn_Forum_Posts (nolock) WHERE PostID = S.MostViewsThreadID),

MostViewsThreadForumID = (SELECT ForumID FROM dnn_Forum_Threads (nolock) WHERE ThreadID = S.MostViewsThreadID),

MostActiveSubject = (SELECT Subject FROM dnn_Forum_Posts (nolock) WHERE PostID = S.MostActiveThreadID),

MostActiveThreadForumID = (SELECT ForumID FROM dnn_Forum_Threads (nolock) WHERE ThreadID = S.MostActiveThreadID),

MostActiveUserAlias = (SELECT Alias FROM dnn_Forum_Users (nolock) WHERE UserID = S.MostActiveUserID)

dnn_Forum_Statistics_Site S (nolock) /*THIS WILL RESOLVE CORRECTLY TO THE DEFAULT SCHEMA*/

ModuleID = @ModuleID

ORDER BY CreatedDate DESC

SELECT DISTINCT TOP 10

U.*,

FUR.Alias,

FUR.IsTrusted,

FUR.PostCount,

FUR.EnableOnlineStatus

FROM

dnn_Forum_Users FUR (nolock),

dnn_Users U (nolock),

dnn_UserPortals UP (nolock),

dnn_Tabs DT (nolock),

dnn_TabModules TM (nolock),

dnn_Modules DM (nolock)

WHERE

FUR.UserID = U.UserID AND

U.UserID = UP.UserID AND

UP.PortalID = DT.PortalID AND

DT.TabID = TM.TabID AND

TM.ModuleID = DM.ModuleID AND

DM.ModuleID = @ModuleID AND

U.UserName <> 'NNTP_NEWSGROUP_USER' AND

FUR.PostCount > 0

ORDER BY

FUR.PostCount DESC

SELECT TOP 10

U.*,

FUR.Alias,

FUR.IsTrusted,

FUR.PostCount,

M.PostsModerated

dnn_Users U (nolock),

dnn_UserPortals UP (nolock),

dnn_Forum_Users FUR (nolock),

dnn_Forum_Moderators M (nolock),

dnn_Tabs DT (nolock),

dnn_TabModules TM (nolock),

dnn_Modules DM (nolock)

WHERE

U.UserID = FUR.UserID AND

FUR.UserID = M.UserID AND

U.UserID = UP.UserID AND

UP.PortalID = DT.PortalID AND

DT.TabID = TM.TabID AND

TM.TabID = DM.ModuleID AND

DM.ModuleID = @ModuleID AND

M.PostsModerated > 0

ORDER BY

PostsModerated DESC

Description,

dnn_Forum_ModerationAction (nolock)

ORDER BY

TotalActions DESC

Both are specified in the same schema which is the default schema for the user that is invoking the stored procedure. Notwithstanding good practise or otherwise (we didn't develop the SP, rather are porting an existing DB from 2000-> 2005, the behaviour in this case is different between the two versions.

The only explanation I can gather from this is that when executing the EXEC statement within a stored procedure, SQL server is changing the context and losing the reference to the default schema. Confirmation of this would be nice.

This doesn't explain the behaviour of the EXEC statement within an SP. Each of the SP's within the DB are upgraded with the new schema. It is only when the EXEC statement is met within a stored procedure that SQL server forgets the user context and the schema it should be using. My question therefore to determine if this is expected behaviour or is a bug in the SQL server engine.

Colm

Thank you for the example. It makes perfect sense to me but I'm afraid my scenario still doesn't!

Here are some details about the db config and object ownership:

• I've a login called colm3rd_mssql which uses the database by default
• I've a user called colm3rd_mssql within the database with a default schema of colm3rd_mssql.
• The .net connection string specifies the db and logs in with the user colm3rd_mssql.
• All user stored procedures in the DB are owned by the schema colm3rd_mssql

Shouldn't this be enough for the exec statement within a procedure to have identified that it should use colm3rd_mssql as the default schema The only part I can assume is missing is that the "execute as" component is unknown when the exec statement is called.
I guess I will need to modify these procedures I guess to work around this but I am feeling more inclined to believe that there is something awry here with the DB Engine.

Rgds and Thks

Colm

OK! Thanks to your pointers I think I've figured out a solution to this. Seems that I need to have a dblogin and dbuser that has a different name to the schema. The db restore had create all of the same name by default. I created a seperate login and mapped this to the same user within the db. Once this is mapped to the schema that was created during the import everything worked as expected and the EXEC statements worked out ok.

Rgds

Colm