|
Home > Archive > Microsoft SQL Server forum > March 2005 > owner data objects
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
owner data objects
|
|
| zMatteo 2005-03-30, 9:41 am |
| 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!!
| |
| Erland Sommarskog 2005-03-30, 9:41 am |
| 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
| |
| zMatteo 2005-03-31, 7:01 am |
| Erland Sommarskog <esquel@sommarskog.se> wrote in message news:< Xns9629B0769BAFBYazo
rman@127.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
|
|
|
|
|