how to remove schema user name from table while using it in a Query

[mmuzaffar]

Hi All

I want to know how to remove the schema username from a table name while using that table in a query

for example currently i am logged in with username abc in sql server 2000
when i select a table in database test123
i have to attach the username with table name i.e abc.table name in order to get the results otherwise the query analyzer returns an error if i only use the table name "Invalid object name 'tablename"

the user abc is the owner of that database with db_datareade and db_datawrite access permission but it is still asking for username to be attached with table name

earlier reposnes will be highly appreciated

thanks

 

[SQLBill]

Does 'abc' own the database? I'm guessing the answer is NO. So, when you do a query against a database, SQL Server looks for the table using the database owner's name. Since, the db owner is different, it won't find the table you want.

-SQLBill

Posting advice: FAQ481-4875

 

[mmuzaffar]

Thanks bill for your quick reply, to answere your question does "abc" own the database , yes thats true user "abc" owns the database as well as the particular table to which i am running a select statement
the query returns the results when ever i user the username.tablename together
it gives error "invalid object name "tablename" when ever i remove the user "abc" from the query
i hope i explained clearly now the problem i am facing

thanks

 

[gmmastros]

If you change the object owner to dbo, then you won't get this problem. I recently ran in to a similar issue and wrote this script to correct the problem.

I am a little hesitant to just 'toss this out there' because it can wreak total havoc on a database. Proceed with caution. If this is a critical database, then do not run this script. When changing the object ownership, EVERYTHING should be re-tested to make sure it is functioning properly. In fact, I would ONLY recommend this script if you are in the early stages of a new development effort. Even if this is a new development project, I STRONGLY urge you to make a full backup of yopur database before running this script.

In my case, I had a handful of update procedures that created new 'temp' tables, copied data from real tables, then dropped the existing tables and re-named the temp tables. It is a rather long series of scripts, but unfortunately caused object ownerships to change. In my case, the owner should have been dbo. All the sprocs and functions expected the objects to be owned by dbo, but the update script went awry. I wrote this script to fix the problem, and it worked beautifully.

Declare @NewOwner VarChar(100)
Set @NewOwner = 'dbo'
Declare @Objects 
Table   (RowId Integer Identity(1,1), 
        ObjectName VarChar(300), 
        OldOwner VarChar(100)) 

Insert 
Into   @Objects
Select table_name, table_schema
From   information_schema.tables
Where  Table_Schema != @NewOwner 

Insert
Into   @Objects
Select specific_name, 
       specific_schema
From   information_Schema.routines 
Where  specific_schema != @NewOwner 

Declare @i Integer
Declare @Max Integer
Declare @ObjectName VarChar(300)
Declare @OldOwner VarChar(100)
Declare @SQL VarChar(1000) 
Select @i = 1,
       @Max = Max(RowId)
From   @Objects 

While @i <= @Max
  Begin
    Select @ObjectName = ObjectName,
           @OldOwner = OldOwner
    From   @Objects
    Where  RowId = @i 

    Set @SQL = 'sp_changeobjectowner ''' + @OldOwner + '.' + @ObjectName + ''', ''' + @NewOwner + '''' 
    Exec (@SQL) 

    Set @i = @i + 1
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mmuzaffar]

Thanks George for your help, actually i am bit hesitant at this stage to run this script as the database is in production and also i dont understand one thing still if the user owns the database and when u are accessing the database why u need to specify the schema/username with the table name in order to fetch the data.

 

[mrdenny]

Because when you access the database SQL only checks objects of two owners. It checks the owner dbo and it checks your personal objects. Because of this you either need to specify the object owner (in this case abc) or you need to change the owner of all the objects from abc to dbo.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com