owner data objects

[zMatteo]

I've a problem.

In my MSSql db I've some tables named username.mytabl e 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!!

[Erland Sommarskog]

Re: owner data objects

zMatteo (origma@edpsist em.it) writes:[color=blue]
> I've a problem.
>
> In my MSSql db I've some tables named username.mytabl e 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?[/color]

SELECT * FROM pippo.mytable

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[zMatteo]

Re: owner data objects

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns9629B0 769BAFBYazorman @127.0.0.1>...[color=blue]
> zMatteo (origma@edpsist em.it) writes:[color=green]
> > I've a problem.
> >
> > In my MSSql db I've some tables named username.mytabl e 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?[/color]
>
> SELECT * FROM pippo.mytable[/color]


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