Why do I have to specify owner?

Why do I have to specify owner?

Post by brett » Thu, 16 Aug 2001 13:13:34



I'm transferring SPs from SQL Server 7 to SQL Server 2000.  I execute one of
the transferred SPs and get an error that the base table was not found. I
put the table owner name in my SP and it works.

Why now that I'm in SQL Server 2000 I have to specify the object name?

Also, I have to many SPs to transfer then go in and manually modify (add
object name on tables).  Can this be automated somehow?

Thanks,
brettr

 
 
 

Why do I have to specify owner?

Post by DaveSat » Thu, 16 Aug 2001 13:48:42


Take a look at who owns the tables - is it dbo ? If not here is some code to
generate the script to change the owner

select "EXEC sp_changeobjectowner 'plumtree." + table_name + "','dbo'"
FROM information_schema.tables
where TABLE_SCHEMA = 'whoever'
AND TABLE_TYPE = 'BASE TABLE'
--
HTH,
David Satz
SQL Server MVP
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------


Quote:> I'm transferring SPs from SQL Server 7 to SQL Server 2000.  I execute one
of
> the transferred SPs and get an error that the base table was not found. I
> put the table owner name in my SP and it works.

> Why now that I'm in SQL Server 2000 I have to specify the object name?

> Also, I have to many SPs to transfer then go in and manually modify (add
> object name on tables).  Can this be automated somehow?

> Thanks,
> brettr


 
 
 

Why do I have to specify owner?

Post by brett » Thu, 16 Aug 2001 17:10:11


The table owner is user "brettr".  The SP owner is "dbo". I think that is my
problem. Different owners for the different objects.

Please explain the parameters in your code below.  Is plumtree a user?

What is "whoever"?

What is "BASE TABLE"?

Thanks,
brettr

> Take a look at who owns the tables - is it dbo ? If not here is some code
to
> generate the script to change the owner

> select "EXEC sp_changeobjectowner 'plumtree." + table_name + "','dbo'"
> FROM information_schema.tables
> where TABLE_SCHEMA = 'whoever'
> AND TABLE_TYPE = 'BASE TABLE'
> --
> HTH,
> David Satz
> SQL Server MVP
> Principal Software Engineer
> Hyperion Solutions
> { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS }
> (Please reply to group only - emails answered rarely)
> -----------------------------------------------------------------



> > I'm transferring SPs from SQL Server 7 to SQL Server 2000.  I execute
one
> of
> > the transferred SPs and get an error that the base table was not found.
I
> > put the table owner name in my SP and it works.

> > Why now that I'm in SQL Server 2000 I have to specify the object name?

> > Also, I have to many SPs to transfer then go in and manually modify (add
> > object name on tables).  Can this be automated somehow?

> > Thanks,
> > brettr

 
 
 

Why do I have to specify owner?

Post by brett » Thu, 16 Aug 2001 17:37:11


Actually, this one works perfect:

EXEC sp_changeobjectowner 'brettr.clients', 'dbo'


> The table owner is user "brettr".  The SP owner is "dbo". I think that is
my
> problem. Different owners for the different objects.

> Please explain the parameters in your code below.  Is plumtree a user?

> What is "whoever"?

> What is "BASE TABLE"?

> Thanks,
> brettr


> > Take a look at who owns the tables - is it dbo ? If not here is some
code
> to
> > generate the script to change the owner

> > select "EXEC sp_changeobjectowner 'plumtree." + table_name + "','dbo'"
> > FROM information_schema.tables
> > where TABLE_SCHEMA = 'whoever'
> > AND TABLE_TYPE = 'BASE TABLE'
> > --
> > HTH,
> > David Satz
> > SQL Server MVP
> > Principal Software Engineer
> > Hyperion Solutions
> > { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
> VSS }
> > (Please reply to group only - emails answered rarely)
> > -----------------------------------------------------------------



> > > I'm transferring SPs from SQL Server 7 to SQL Server 2000.  I execute
> one
> > of
> > > the transferred SPs and get an error that the base table was not
found.
> I
> > > put the table owner name in my SP and it works.

> > > Why now that I'm in SQL Server 2000 I have to specify the object name?

> > > Also, I have to many SPs to transfer then go in and manually modify
(add
> > > object name on tables).  Can this be automated somehow?

> > > Thanks,
> > > brettr

 
 
 

Why do I have to specify owner?

Post by DaveSat » Thu, 16 Aug 2001 22:33:14


TABLE_SCHEMA column holds the owner

'BASE TABLE' basically means table vs. a view

select "EXEC sp_changeobjectowner ''whoever." + table_name + "','dbo'"
FROM information_schema.tables
where TABLE_SCHEMA = 'whoever'
AND TABLE_TYPE = 'BASE TABLE'


> Actually, this one works perfect:

> EXEC sp_changeobjectowner 'brettr.clients', 'dbo'



> > The table owner is user "brettr".  The SP owner is "dbo". I think that
is
> my
> > problem. Different owners for the different objects.

> > Please explain the parameters in your code below.  Is plumtree a user?

> > What is "whoever"?

> > What is "BASE TABLE"?

> > Thanks,
> > brettr


> > > Take a look at who owns the tables - is it dbo ? If not here is some
> code
> > to
> > > generate the script to change the owner

> > > select "EXEC sp_changeobjectowner 'plumtree." + table_name + "','dbo'"
> > > FROM information_schema.tables
> > > where TABLE_SCHEMA = 'whoever'
> > > AND TABLE_TYPE = 'BASE TABLE'
> > > --
> > > HTH,
> > > David Satz
> > > SQL Server MVP
> > > Principal Software Engineer
> > > Hyperion Solutions
> > > { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
> > VSS }
> > > (Please reply to group only - emails answered rarely)
> > > -----------------------------------------------------------------



> > > > I'm transferring SPs from SQL Server 7 to SQL Server 2000.  I
execute
> > one
> > > of
> > > > the transferred SPs and get an error that the base table was not
> found.
> > I
> > > > put the table owner name in my SP and it works.

> > > > Why now that I'm in SQL Server 2000 I have to specify the object
name?

> > > > Also, I have to many SPs to transfer then go in and manually modify
> (add
> > > > object name on tables).  Can this be automated somehow?

> > > > Thanks,
> > > > brettr

 
 
 

1. Retrieving data without specifying table owner

I'm working on the conversion of a SQL Anywhere database to Oracle.  In SQL
Anywhere, if you select from a table name and don't specify the owner, it
will still find the table as long as it is visible to you.

Now, good programming technique would probably insist that you specify the
table owner in each select anyway, but I'm left with miles of legacy code
that didn't.  If I can avoid rewriting each SQL script, it would make me
happy.

Is there anyway to make Oracle find the tables without specifying the owner
name.

I'll give some pseudo-code examples below to describe my problem in more
detail.

////////////////////////////////////////
SQL Anywhere:

GRANT CONNECT TO SystemOwner IDENTIFIED BY password;
GRANT DBA TO SystemOwner;
GRANT GROUP TO SystemOwner;
CREATE TABLE SystemOwner.Student
    (
    Name CHAR(30)
    );

GRANT CONNECT TO SampleUser IDENTIFIED BY password;
GRANT DBA TO SampleUser;
GRANT MEMBERSHIP IN GROUP SystemOwner to SampleUser;

CONNECT SampleUser;
SELECT * FROM Student;

The select will return 0 rows (that's good).

////////////////////////////////////////
Oracle:

GRANT CONNECT TO SystemOwner IDENTIFIED BY password;
GRANT DBA TO SystemOwner;
CREATE TABLE SystemOwner.Student
    (
    Name CHAR(30)
    );

GRANT CONNECT TO SampleUser IDENTIFIED BY password;
GRANT DBA TO SampleUser;

CONNECT SampleUser/password;
SELECT * FROM Student;

The following error will occur:
ERROR at line 1:
ORA-00942: table or view does not exist

Thanks for reading.  Any help would be appreciated

Jeff Van Dusseldorp
PlannSoft

2. CRACK THIS

3. Forced to specify table's owner

4. XSLT id() doesn't work - validation against XSD

5. !!! Problem using column prefix without specifying table owner

6. SELECT Problem

7. Create a job in T-SQL and specify a owner

8. trigger an independent procedure

9. Problem using column prefix without specifying table owner

10. can't find sp without specifying the owner

11. Problem using column prefix without specifying table owner

12. Why SELECT OWNER.TABLENAME.COLUMNNAME not working ?