470,643 Members | 1,359 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,643 developers. It's quick & easy.

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!!
Jul 23 '05 #1
2 1346
zMatteo (or****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
zMatteo (or****@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

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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Brian | last post: by
1 post views Thread by Mike Mascari | last post: by
7 posts views Thread by Laurence | last post: by
2 posts views Thread by aj | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.