Hi guys

I have a login to my SQL Server 2005 database, and my database has a user of
the same name. I have linked the login to this user, and set the default
schema to be MySchema.

Then, I log in to SSMS as this login/user and execute a stored procedure
like this:

DECLARE @return_value int
EXEC @return_value = [MySproc]
....

to which I get an error to the effect that the stored procedure cannot be
found. If I qualify the call with the schema name then it finds it. It
appears that it is defaulting to dbo, even though the user has a default
schema of MySchema.

Am I missing something here?

TIA

Charles

"Charles Law" <blank@nowhere.com> wrote in message
news:uTQig8J0IHA.4848@TK2MSFTNGP05.phx.gbl...
> Hi guys
>
> I have a login to my SQL Server 2005 database, and my database has a user
> of the same name. I have linked the login to this user, and set the
> default schema to be MySchema.
>
> Then, I log in to SSMS as this login/user and execute a stored procedure
> like this:
>
> DECLARE @return_value int
> EXEC @return_value = [MySproc]
> ...
>
> to which I get an error to the effect that the stored procedure cannot be
> found. If I qualify the call with the schema name then it finds it. It
> appears that it is defaulting to dbo, even though the user has a default
> schema of MySchema.
>
> Am I missing something here?
>
> TIA
>
> Charles
>

What is the name of MySproc?

Why not post a script that recreates the problem including all DDL?

John

Hi John

The name is academic. Any name will reproduce the problem, e.g. MySproc1. To
reproduce the problem you can choose any of your own sprocs that is not in
the dbo schema, create a login and user as I have described, log in with
that user name and try to run the sproc without qualifying the name.

If you have one that works as I have described, perhaps you could post it
for me to try.

Charles


"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:B9612EF0-542B-4C8E-BB93-ADFB7F38C2DF@microsoft.com...
>
> "Charles Law" <blank@nowhere.com> wrote in message
> news:uTQig8J0IHA.4848@TK2MSFTNGP05.phx.gbl...
>> Hi guys
>>
>> I have a login to my SQL Server 2005 database, and my database has a user
>> of the same name. I have linked the login to this user, and set the
>> default schema to be MySchema.
>>
>> Then, I log in to SSMS as this login/user and execute a stored procedure
>> like this:
>>
>> DECLARE @return_value int
>> EXEC @return_value = [MySproc]
>> ...
>>
>> to which I get an error to the effect that the stored procedure cannot be
>> found. If I qualify the call with the schema name then it finds it. It
>> appears that it is defaulting to dbo, even though the user has a default
>> schema of MySchema.
>>
>> Am I missing something here?
>>
>> TIA
>>
>> Charles
>>
>
> What is the name of MySproc?
>
> Why not post a script that recreates the problem including all DDL?
>
> John

What I didn't mention is that the login is sysadmin.

I now find that this behaviour is peculiar to the sysadmin role, so I can't
do what I wanted. It seems that sysadmin, like dbo, always maps to the dbo
schema. This is very misleading, as it looks for all the world like the user
has a default schema of MySchema, except that gets overridden by the
sysadmin role.

Oh well. If there were nothing left to learn, what would we do with
ourselves all day!

Charles


"Charles Law" <blank@nowhere.com> wrote in message
news:uTQig8J0IHA.4848@TK2MSFTNGP05.phx.gbl...
> Hi guys
>
> I have a login to my SQL Server 2005 database, and my database has a user
> of the same name. I have linked the login to this user, and set the
> default schema to be MySchema.
>
> Then, I log in to SSMS as this login/user and execute a stored procedure
> like this:
>
> DECLARE @return_value int
> EXEC @return_value = [MySproc]
> ...
>
> to which I get an error to the effect that the stored procedure cannot be
> found. If I qualify the call with the schema name then it finds it. It
> appears that it is defaulting to dbo, even though the user has a default
> schema of MySchema.
>
> Am I missing something here?
>
> TIA
>
> Charles
>
>

"Charles Law" <blank@nowhere.com> wrote in message
news:uqN5thN0IHA.5520@TK2MSFTNGP04.phx.gbl...
> Hi John
>
> The name is academic. Any name will reproduce the problem, e.g. MySproc1.
> To reproduce the problem you can choose any of your own sprocs that is not
> in the dbo schema, create a login and user as I have described, log in
> with that user name and try to run the sproc without qualifying the name.
>
> If you have one that works as I have described, perhaps you could post it
> for me to try.
>
> Charles
>
>
> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
> news:B9612EF0-542B-4C8E-BB93-ADFB7F38C2DF@microsoft.com...
>>
>> "Charles Law" <blank@nowhere.com> wrote in message
>> news:uTQig8J0IHA.4848@TK2MSFTNGP05.phx.gbl...
>>> Hi guys
>>>
>>> I have a login to my SQL Server 2005 database, and my database has a
>>> user of the same name. I have linked the login to this user, and set the
>>> default schema to be MySchema.
>>>
>>> Then, I log in to SSMS as this login/user and execute a stored procedure
>>> like this:
>>>
>>> DECLARE @return_value int
>>> EXEC @return_value = [MySproc]
>>> ...
>>>
>>> to which I get an error to the effect that the stored procedure cannot
>>> be found. If I qualify the call with the schema name then it finds it.
>>> It appears that it is defaulting to dbo, even though the user has a
>>> default schema of MySchema.
>>>
>>> Am I missing something here?
>>>
>>> TIA
>>>
>>> Charles
>>>
>>
>> What is the name of MySproc?
>>
>> Why not post a script that recreates the problem including all DDL?
>>
>> John
>
>
Charles,

If you could be bothered to go through the process of writing a scipt then
it is quite possible that you would realise where you have made a mistake.
If you have not made a mistake the script would anyone answering your
post to easily re-create the problem and therefore they would not have to
waste their time with a half baked description of "do this do that.... " It
is very short sighted to assume that it is academic.

The following works fine and as expected
SELECT @@VERSION
/*
Microsoft SQL Server 2005 - 9.00.3159.00 (X64)
*/
CREATE DATABASE NewUser
GO
USE NewUser
GO
CREATE SCHEMA Schema_For_Charles;
GO
CREATE LOGIN Charles WITH PASSWORD = 'Insecure', DEFAULT_DATABASE = NewUser
GO
CREATE USER CHARLES FROM LOGIN Charles
WITH DEFAULT_SCHEMA = Schema_For_Charles
GO
CREATE PROCEDURE Schema_For_Charles.GetFromsysobjects
AS
SELECT * FROM sysobjects
GO
GRANT EXECUTE ON Schema_For_Charles.GetFromsysobjects TO Charles
GO
EXECUTE AS LOGIN = 'Charles'
GO
-- This works as expected
EXEC GetFromsysobjects
GO
-- This also works
EXEC Schema_For_Charles.GetFromsysobjects
GO
REVERT
GO
-- No longer Charles so default schema is different
EXEC GetFromsysobjects
GO
/*
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'GetFromsysobjects'.
*/
-- This works and returns all objects
EXEC Schema_For_Charles.GetFromsysobjects
GO

Login in to SSMS as Charles has the same results as EXECUTE AS

John

Hi John

It's not that I couldn't be bothered, but as I have now confirmed, the
problem was elsewhere. I posted a reply to that effect earlier today. It was
because the login had sysadmin that the behaviour was odd (to me). But I
take your point, I should be wary of omitting detail just because I think it
unimportant; if I had mentioned the sysadmin bit before someone might have
spotted that to be the problem.

Cheers

Charles


"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:1273D566-80D4-4CC9-8DA6-3FA8C0A284DF@microsoft.com...
>
> "Charles Law" <blank@nowhere.com> wrote in message
> news:uqN5thN0IHA.5520@TK2MSFTNGP04.phx.gbl...
>> Hi John
>>
>> The name is academic. Any name will reproduce the problem, e.g. MySproc1.
>> To reproduce the problem you can choose any of your own sprocs that is
>> not in the dbo schema, create a login and user as I have described, log
>> in with that user name and try to run the sproc without qualifying the
>> name.
>>
>> If you have one that works as I have described, perhaps you could post it
>> for me to try.
>>
>> Charles
>>
>>
>> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
>> news:B9612EF0-542B-4C8E-BB93-ADFB7F38C2DF@microsoft.com...
>>>
>>> "Charles Law" <blank@nowhere.com> wrote in message
>>> news:uTQig8J0IHA.4848@TK2MSFTNGP05.phx.gbl...
>>>> Hi guys
>>>>
>>>> I have a login to my SQL Server 2005 database, and my database has a
>>>> user of the same name. I have linked the login to this user, and set
>>>> the default schema to be MySchema.
>>>>
>>>> Then, I log in to SSMS as this login/user and execute a stored
>>>> procedure like this:
>>>>
>>>> DECLARE @return_value int
>>>> EXEC @return_value = [MySproc]
>>>> ...
>>>>
>>>> to which I get an error to the effect that the stored procedure cannot
>>>> be found. If I qualify the call with the schema name then it finds it.
>>>> It appears that it is defaulting to dbo, even though the user has a
>>>> default schema of MySchema.
>>>>
>>>> Am I missing something here?
>>>>
>>>> TIA
>>>>
>>>> Charles
>>>>
>>>
>>> What is the name of MySproc?
>>>
>>> Why not post a script that recreates the problem including all DDL?
>>>
>>> John
>>
>>
> Charles,
>
> If you could be bothered to go through the process of writing a scipt then
> it is quite possible that you would realise where you have made a mistake.
> If you have not made a mistake the script would anyone answering your
> post to easily re-create the problem and therefore they would not have to
> waste their time with a half baked description of "do this do that.... "
> It is very short sighted to assume that it is academic.
>
> The following works fine and as expected
> SELECT @@VERSION
> /*
> Microsoft SQL Server 2005 - 9.00.3159.00 (X64)
> */
> CREATE DATABASE NewUser
> GO
> USE NewUser
> GO
> CREATE SCHEMA Schema_For_Charles;
> GO
> CREATE LOGIN Charles WITH PASSWORD = 'Insecure', DEFAULT_DATABASE =
> NewUser
> GO
> CREATE USER CHARLES FROM LOGIN Charles
> WITH DEFAULT_SCHEMA = Schema_For_Charles
> GO
> CREATE PROCEDURE Schema_For_Charles.GetFromsysobjects
> AS
> SELECT * FROM sysobjects
> GO
> GRANT EXECUTE ON Schema_For_Charles.GetFromsysobjects TO Charles
> GO
> EXECUTE AS LOGIN = 'Charles'
> GO
> -- This works as expected
> EXEC GetFromsysobjects
> GO
> -- This also works
> EXEC Schema_For_Charles.GetFromsysobjects
> GO
> REVERT
> GO
> -- No longer Charles so default schema is different
> EXEC GetFromsysobjects
> GO
> /*
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'GetFromsysobjects'.
> */
> -- This works and returns all objects
> EXEC Schema_For_Charles.GetFromsysobjects
> GO
>
> Login in to SSMS as Charles has the same results as EXECUTE AS
>
> John

"Charles Law" <blank@nowhere.com> wrote in message
news:%23jebj3U0IHA.5944@TK2MSFTNGP04.phx.gbl...
> Hi John
>
> It's not that I couldn't be bothered, but as I have now confirmed, the
> problem was elsewhere. I posted a reply to that effect earlier today. It
> was because the login had sysadmin that the behaviour was odd (to me). But
> I take your point, I should be wary of omitting detail just because I
> think it unimportant; if I had mentioned the sysadmin bit before someone
> might have spotted that to be the problem.
>
> Cheers
>
> Charles
>
Hi Charles

The production of the script would have probably brought a quicker solution
to the problem as you would have needed an explicit call to
sp_addsrvrolemember. You would need to question why this user needs such a
high permission where they can do most things on the server?
I it also good practice to use two part naming, this can matching
queries in cache and avoids excessive searching for objects.

John