SP2 blues: owner object not seen by owner unless fully qualified

SP2 blues: owner object not seen by owner unless fully qualified

Post by Eric Robisha » Sat, 16 Feb 2002 02:31:09



Oh man...I ran sql server SP2 and now I'm in trouble:

I have tons of code that's referencing Tables and SPs via ADO and VB...we're
talking 3 years of apps and applets...

Before the SP2, if a table owner was connected, my SQL queries did not have
to specifically reference Table objects owned by that owner by owner name.

ie:  "Select * from TableName" USED to work, EVEN if TableName was owned by,
say Bob, as long as Bob was connecting.

Since SP2.... when Bob is connected, ADO can't find the table, unless I
reference the owner: "Select * from Bob.TableName"

I've learned that its best to have DBO own objects, so I do that from now
on, but I've got 100s of tables and procedures and 100s of apps that
reference tables without the full qualifier.  Is there anything I can do,
short of uninstalling SP2, or changing all my apps, that will allow BOB to
see BOB's tables without referencing Bob.Tablename?  There's got to be some
SQL setting that allows this?

Desperate Eric.

 
 
 

SP2 blues: owner object not seen by owner unless fully qualified

Post by jaco » Sat, 16 Feb 2002 03:34:19


Eric,

Make a backup of your database before running this !!!
Just incase it doesn't work for you, it should though.
Execute the below script,copy the output and then run it
against the database that has the user owned objects. It
will change the object owner to dbo.

select
        'exec
sp_changeobjectowner'+' '+' '''+syu.name+'.'+syo.name+''' '
 + ','+'''dbo''' + '
go'

From sysobjects syo inner join sysusers syu on syo.uid =
syu.uid

Where syo.uid not in (1,3)and type = 'u'

Now execute the below script to grant permissions to the
tables.

select 'Grant Select on ' +name+ ' to Bob' -- change Bob
to whoever needs the permissions
from sysobjects
where xtype = 'u'-- Change u to whatever type of object
needs the permissions
                 -- If it is a sp change Select up to
Execute

This should work fine for you problem..
Jake

Quote:>-----Original Message-----
>Oh man...I ran sql server SP2 and now I'm in trouble:

>I have tons of code that's referencing Tables and SPs via
ADO and VB...we're
>talking 3 years of apps and applets...

>Before the SP2, if a table owner was connected, my SQL

queries did not have
Quote:>to specifically reference Table objects owned by that

owner by owner name.
Quote:

>ie:  "Select * from TableName" USED to work, EVEN if

TableName was owned by,
Quote:>say Bob, as long as Bob was connecting.

>Since SP2.... when Bob is connected, ADO can't find the
table, unless I
>reference the owner: "Select * from Bob.TableName"

>I've learned that its best to have DBO own objects, so I
do that from now
>on, but I've got 100s of tables and procedures and 100s
of apps that
>reference tables without the full qualifier.  Is there
anything I can do,
>short of uninstalling SP2, or changing all my apps, that
will allow BOB to
>see BOB's tables without referencing Bob.Tablename?  

There's got to be some
Quote:>SQL setting that allows this?

>Desperate Eric.

>.


 
 
 

SP2 blues: owner object not seen by owner unless fully qualified

Post by Eric Robisha » Sat, 16 Feb 2002 04:43:45


Unfortunately, this won't work for me because I have some code that
references these objects by their full name: "Select * from bob.TableName",
and some code that doesn't: "Select *  from TableName"

The problem is, before if Bob were connected, SQL Server would return the
table either way. If I change the owner to dbo, then any code that
references the tables by their full name will die.

There's got to be a setting somewhere that tells SQL server to find and
return objects owned by the current user without qualifying the name.

HELP
Eric


Quote:> Eric,

> Make a backup of your database before running this !!!
> Just incase it doesn't work for you, it should though.
> Execute the below script,copy the output and then run it
> against the database that has the user owned objects. It
> will change the object owner to dbo.

> select
> 'exec
> sp_changeobjectowner'+' '+' '''+syu.name+'.'+syo.name+''' '
>  + ','+'''dbo''' + '
> go'

> From sysobjects syo inner join sysusers syu on syo.uid =
> syu.uid

> Where syo.uid not in (1,3)and type = 'u'

> Now execute the below script to grant permissions to the
> tables.

> select 'Grant Select on ' +name+ ' to Bob' -- change Bob
> to whoever needs the permissions
> from sysobjects
> where xtype = 'u'-- Change u to whatever type of object
> needs the permissions
> -- If it is a sp change Select up to
> Execute

> This should work fine for you problem..
> Jake
> >-----Original Message-----
> >Oh man...I ran sql server SP2 and now I'm in trouble:

> >I have tons of code that's referencing Tables and SPs via
> ADO and VB...we're
> >talking 3 years of apps and applets...

> >Before the SP2, if a table owner was connected, my SQL
> queries did not have
> >to specifically reference Table objects owned by that
> owner by owner name.

> >ie:  "Select * from TableName" USED to work, EVEN if
> TableName was owned by,
> >say Bob, as long as Bob was connecting.

> >Since SP2.... when Bob is connected, ADO can't find the
> table, unless I
> >reference the owner: "Select * from Bob.TableName"

> >I've learned that its best to have DBO own objects, so I
> do that from now
> >on, but I've got 100s of tables and procedures and 100s
> of apps that
> >reference tables without the full qualifier.  Is there
> anything I can do,
> >short of uninstalling SP2, or changing all my apps, that
> will allow BOB to
> >see BOB's tables without referencing Bob.Tablename?
> There's got to be some
> >SQL setting that allows this?

> >Desperate Eric.

> >.

 
 
 

SP2 blues: owner object not seen by owner unless fully qualified

Post by Dan Guzma » Sat, 16 Feb 2002 11:27:03


Eric, this is not normal behavior and I don't believe there is a
configuration option to change this.

You might try to login via Query Analyzer as 'bob' and then 'SELECT
USER' to ensure 'bob' is returned.  One possibility is that the login
was added to the sysadmin server role.  'dbo' will be returned if this
is the case.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> Oh man...I ran sql server SP2 and now I'm in trouble:

> I have tons of code that's referencing Tables and SPs via ADO and
VB...we're
> talking 3 years of apps and applets...

> Before the SP2, if a table owner was connected, my SQL queries did not
have
> to specifically reference Table objects owned by that owner by owner
name.

> ie:  "Select * from TableName" USED to work, EVEN if TableName was
owned by,
> say Bob, as long as Bob was connecting.

> Since SP2.... when Bob is connected, ADO can't find the table, unless
I
> reference the owner: "Select * from Bob.TableName"

> I've learned that its best to have DBO own objects, so I do that from
now
> on, but I've got 100s of tables and procedures and 100s of apps that
> reference tables without the full qualifier.  Is there anything I can
do,
> short of uninstalling SP2, or changing all my apps, that will allow
BOB to
> see BOB's tables without referencing Bob.Tablename?  There's got to be
some
> SQL setting that allows this?

> Desperate Eric.

 
 
 

SP2 blues: owner object not seen by owner unless fully qualified

Post by Jacob Dulwort » Sat, 16 Feb 2002 23:50:14


Eric,

Can you copy the error message that you are receiving and
send up?

Jake

Quote:>-----Original Message-----
>Unfortunately, this won't work for me because I have some
code that
>references these objects by their full name: "Select *

from bob.TableName",
Quote:>and some code that doesn't: "Select *  from TableName"

>The problem is, before if Bob were connected, SQL Server
would return the
>table either way. If I change the owner to dbo, then any
code that
>references the tables by their full name will die.

>There's got to be a setting somewhere that tells SQL
server to find and
>return objects owned by the current user without

qualifying the name.

>HELP
>Eric



>> Eric,

>> Make a backup of your database before running this !!!
>> Just incase it doesn't work for you, it should though.
>> Execute the below script,copy the output and then run it
>> against the database that has the user owned objects. It
>> will change the object owner to dbo.

>> select
>> 'exec

sp_changeobjectowner'+' '+' '''+syu.name+'.'+syo.name+''' '

- Show quoted text -

Quote:>>  + ','+'''dbo''' + '
>> go'

>> From sysobjects syo inner join sysusers syu on syo.uid =
>> syu.uid

>> Where syo.uid not in (1,3)and type = 'u'

>> Now execute the below script to grant permissions to the
>> tables.

>> select 'Grant Select on ' +name+ ' to Bob' -- change Bob
>> to whoever needs the permissions
>> from sysobjects
>> where xtype = 'u'-- Change u to whatever type of object
>> needs the permissions
>> -- If it is a sp change Select up to
>> Execute

>> This should work fine for you problem..
>> Jake
>> >-----Original Message-----
>> >Oh man...I ran sql server SP2 and now I'm in trouble:

>> >I have tons of code that's referencing Tables and SPs
via
>> ADO and VB...we're
>> >talking 3 years of apps and applets...

>> >Before the SP2, if a table owner was connected, my SQL
>> queries did not have
>> >to specifically reference Table objects owned by that
>> owner by owner name.

>> >ie:  "Select * from TableName" USED to work, EVEN if
>> TableName was owned by,
>> >say Bob, as long as Bob was connecting.

>> >Since SP2.... when Bob is connected, ADO can't find the
>> table, unless I
>> >reference the owner: "Select * from Bob.TableName"

>> >I've learned that its best to have DBO own objects, so
I
>> do that from now
>> >on, but I've got 100s of tables and procedures and 100s
>> of apps that
>> >reference tables without the full qualifier.  Is there
>> anything I can do,
>> >short of uninstalling SP2, or changing all my apps,
that
>> will allow BOB to
>> >see BOB's tables without referencing Bob.Tablename?
>> There's got to be some
>> >SQL setting that allows this?

>> >Desperate Eric.

>> >.

>.

 
 
 

SP2 blues: owner object not seen by owner unless fully qualified

Post by Eric Robisha » Wed, 20 Feb 2002 07:52:31


thank-you thank-you thank-you.

that was it!  Bob was a member of the sysadmin role.

I had no idea this behavior would occur with the user added to the sysadmin
server role. Why is that, anyhow?

Thanks
Eric Robishaw


> Eric, this is not normal behavior and I don't believe there is a
> configuration option to change this.

> You might try to login via Query Analyzer as 'bob' and then 'SELECT
> USER' to ensure 'bob' is returned.  One possibility is that the login
> was added to the sysadmin server role.  'dbo' will be returned if this
> is the case.

> Hope this helps.

> Dan Guzman
> SQL Server MVP

> -----------------------
> SQL FAQ links (courtesy  Neil Pike):

> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------



> > Oh man...I ran sql server SP2 and now I'm in trouble:

> > I have tons of code that's referencing Tables and SPs via ADO and
> VB...we're
> > talking 3 years of apps and applets...

> > Before the SP2, if a table owner was connected, my SQL queries did not
> have
> > to specifically reference Table objects owned by that owner by owner
> name.

> > ie:  "Select * from TableName" USED to work, EVEN if TableName was
> owned by,
> > say Bob, as long as Bob was connecting.

> > Since SP2.... when Bob is connected, ADO can't find the table, unless
> I
> > reference the owner: "Select * from Bob.TableName"

> > I've learned that its best to have DBO own objects, so I do that from
> now
> > on, but I've got 100s of tables and procedures and 100s of apps that
> > reference tables without the full qualifier.  Is there anything I can
> do,
> > short of uninstalling SP2, or changing all my apps, that will allow
> BOB to
> > see BOB's tables without referencing Bob.Tablename?  There's got to be
> some
> > SQL setting that allows this?

> > Desperate Eric.

 
 
 

SP2 blues: owner object not seen by owner unless fully qualified

Post by Dan Guzma » Wed, 20 Feb 2002 10:14:17


Sysadmin role members are 'dbo' in all databases.  Consequently,
sysadmin role members must specify the owner in order to access non-dbo
owned objects.

BTW, it's not good practice to use a sysadmin role member for routine
application access.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> thank-you thank-you thank-you.

> that was it!  Bob was a member of the sysadmin role.

> I had no idea this behavior would occur with the user added to the
sysadmin
> server role. Why is that, anyhow?

> Thanks
> Eric Robishaw

 
 
 

1. A database owner is not reconized as a database owner

Hi there,

I am very puzzled with the following behaviour.  Just wonder if you have
experienced the same and have a fix for it.

I understand that only a database owner (i.e. dbo) can restore a database.
So what I have done is to change the owner by using

use <databasename>
go
sp_changedbowner '<domain>/<username>'

And when I have confirmed that the NT login '<domain>/<username>' is now a
database owner by using

use <databasename>
go
sp_helpusers

However, when the user '<domain>/username>' try to restore the database,
SQL reports the following error:

"Microsoft SQL-DMO (ODBC SQLState:42000)
Only members of the sysadmn role or the database owner of '<databasename>'
can run RESTORE DATABASE.  Backup or restore operation terminating
abnormally."

But I am in fact the user is in fact a database owner.

However, if the database owner is set to a SQL standard login, I don't have
the above problem.  How can I get rid of the error when an NT login is a
database owner.

Thanks for your help in advance.

ChingTai

2. MSDE, DTS, and Visual Basic -> Just looking for an explanation !?!?!

3. TOP with fully qualified table_name (SS7 SP2)

4. Error handling bug between DTS and SQL Agent

5. Replication of owner qualified tables

6. Toolbar Icon Problem

7. Setting Replication with Owner Qualified tables

8. Error "Couldn't find installable ISAM"

9. Refer to table without qualifying with owner name in SQL 7

10. bug in qualified database.owner.table in sql:relation

11. scalar user-defined function require the function to be owner qualified

12. Accessing another user's table without qualifying table name with the owner-name

13. Fully Qualified Object Names