owner data objects

I've a problem.

In my MSSql db I've some tables named username.mytable and only one
store procedure named dbo.sp;


CREATE TABLE [pippo].[mytable] (
[year]  [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [pluto].[mytable] (
[year]  [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from mytable
GO


but when I try to execute dbo.sp (from pippo or pluto connection) I've
this error (users pippo and pluto are owner):

Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
Invalid object name 'mytable'.

How can I access to pippo's (or pluto's) data from dbo.sp?


thanks!!

zMatteo (origma@edpsistem.it)  writes:
> I've a problem.
>
> In my MSSql db I've some tables named username.mytable and only one
> store procedure named dbo.sp;
>
>
> CREATE TABLE [pippo].[mytable] (
>      [year]  [int] NOT NULL ,
>      [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [pluto].[mytable] (
>      [year]  [int] NOT NULL ,
>      [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE PROCEDURE [dbo].[sp]
> AS
>   select * from mytable
> GO
>
>
> but when I try to execute dbo.sp (from pippo or pluto connection) I've
> this error (users pippo and pluto are owner):
>
>     Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
>     Invalid object name 'mytable'.
>
> How can I access to pippo's (or pluto's) data from dbo.sp?

SELECT * FROM pippo.mytable

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:< Xns9629B0769BAFBYazo
rman@12
7.0.0.1>...
> zMatteo (origma@edpsistem.it) writes: 
>
> SELECT * FROM pippo.mytable


but for user pluto i'd make a new store procedure...
I'd resolve the problem (attention: it's ok only for not sysadmin users)!!:

CREATE TABLE [dbo].[mytable] (
[user]  [smallint] NOT NULL,
[year]  [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO


CREATE VIEW [dbo].[myview]
AS
select * from mytable
where user=user_id()
GO


CREATE PROCEDURE [dbo].[sp]
AS
select * from myview
GO