Change DB owner/Table owner

Change DB owner/Table owner

Post by Toni Eibne » Fri, 01 Jan 1999 04:00:00



I know it is possible to change the database owner, but am I able to
change
the owner of a table as well?  Currently, the owner of the database and
tables
is EXAV.  The problem is for the programmers...  this is the only table
owned by
anyone other than DBO.  We've got some code that is supposed to work
for both tables and it causes them some coding problems because that
qualifier
isn't the same.  For the tables owned by EXAV, they have to code it
EXAV.tablename
and for the tables owned by DBO, they just code the tablename.

Is there anything I can do short of creating a new database and copying
the data over?
Thanks for the advice!
Toni Eibner

 
 
 

Change DB owner/Table owner

Post by Steve Robinso » Fri, 01 Jan 1999 04:00:00


Toni,

In the sysobjects table there is  a column uid which defines the ownership
of the table.  By changing this id to the uid of the user in sysusers you
which to pass ownership to you will change the ownership of the table eg

select name,uid from sysobjects where name = "<Table Name>"
select nam, uid  from sysusers where name = "<New Owner Name>"
go

exec sp_configure "allow updates",1
reconfigure with override
go
update sysobjects set uid = <New UID> where name = "<Table Name>"
go
exec sp_configure "allow updates",0
reconfigure with override
go

This should solve the problem.

Make sure you test this first though in the dev area.  Let me know if you
have any problems.

Steve Robinson
SQLServer MVP



>I know it is possible to change the database owner, but am I able to
>change
>the owner of a table as well?  Currently, the owner of the database and
>tables
>is EXAV.  The problem is for the programmers...  this is the only table
>owned by
>anyone other than DBO.  We've got some code that is supposed to work
>for both tables and it causes them some coding problems because that
>qualifier
>isn't the same.  For the tables owned by EXAV, they have to code it
>EXAV.tablename
>and for the tables owned by DBO, they just code the tablename.

>Is there anything I can do short of creating a new database and copying
>the data over?
>Thanks for the advice!
>Toni Eibner


 
 
 

Change DB owner/Table owner

Post by Toni Eibne » Fri, 01 Jan 1999 04:00:00


Steve,

I tested it out and it works!  You are a genius!  You definitely saved me
quite a bit of time!

Thank you so much and Happy New Year!
Toni Eibner


> Toni,

> In the sysobjects table there is  a column uid which defines the ownership
> of the table.  By changing this id to the uid of the user in sysusers you
> which to pass ownership to you will change the ownership of the table eg

> select name,uid from sysobjects where name = "<Table Name>"
> select nam, uid  from sysusers where name = "<New Owner Name>"
> go

> exec sp_configure "allow updates",1
> reconfigure with override
> go
> update sysobjects set uid = <New UID> where name = "<Table Name>"
> go
> exec sp_configure "allow updates",0
> reconfigure with override
> go

> This should solve the problem.

> Make sure you test this first though in the dev area.  Let me know if you
> have any problems.

> Steve Robinson
> SQLServer MVP



> >I know it is possible to change the database owner, but am I able to
> >change
> >the owner of a table as well?  Currently, the owner of the database and
> >tables
> >is EXAV.  The problem is for the programmers...  this is the only table
> >owned by
> >anyone other than DBO.  We've got some code that is supposed to work
> >for both tables and it causes them some coding problems because that
> >qualifier
> >isn't the same.  For the tables owned by EXAV, they have to code it
> >EXAV.tablename
> >and for the tables owned by DBO, they just code the tablename.

> >Is there anything I can do short of creating a new database and copying
> >the data over?
> >Thanks for the advice!
> >Toni Eibner

 
 
 

1. Changing the DB owner

How do you change the owner of a database?

I have 3 databases that were created by users(SQL Server
Logins, not Windows Authinticated users) and I want to
delete those users and make them NT Users. I can not
delete them because they are the owners of those DBs.

Thanks,
ThomasLL

2. P4W forms question

3. DB Owner changed after Restore

4. In need of DB-LIBRARY files

5. change db owner from sa to another login with sysadmin provilege

6. Determination of Table Load Sequence

7. Changing all DB object owners to DBO

8. SQL 2000 STANDARD TO SAN-IT WILL NOT WORK

9. cannot change DB-owner

10. How to change DB owner

11. change Db owner

12. Change the owner of a DB?