MS-SQL tables ownership

I have a production databse with several tables with dbo as owner.  I backed up the database and restore it in the production server. I need to change the ownership of all the tables in the production server to a "specifuser" user name.
How to I change the ownership of the complete datbase and tables to another user?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
to change the ownership of the database, use the sp_changedbowner  procedure
to change the ownership of an object inside the database, use the sp_changeobjectowner procedure
to make that for all the objects, you can query the sysobjects system table, however, ensure that you don't change the system objects...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT @old = 'oldOwner_CHANGE_THIS' ,
@new = 'dbo' ,

EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''EXECUTE sp_MSforeachtable @sql
The same can be done to stored procedures. This example works differently though. It doesn't actually make the change. Save the results in text and then paste the result back into Query Analyzer and run it.
DECLARE @oldOwner sysname, @newOwner sysname
SELECT @oldOwner = 'oldOwner_CHANGE_THIS' ,
@newOwner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''from INFORMATION_SCHEMA.ROUTINES a

sweetbuttercupAuthor Commented:
when I run EXEC sp_changedbowner 'mydb'
I get this error

The proposed new database owner is already a user or aliased in the database.

When I run
sp_changeobjectowner 'table1' , 'newowner'

I get
Database principal or schema 'newowner' does not exist in this database.

I need more details in how to change all tables there are around 100 tables to change.. No store proc. only tables.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Aneesh RetnakaranDatabase AdministratorCommented:
> when I run EXEC sp_changedbowner 'mydb'

in order to change the database owner , use

use MyDB
EXEC sp_changedbowner 'newOwner'

> sp_changeobjectowner 'table1' , 'newowner'

you have to replace the newowner with the name of new owner

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please confirm that you are using SQL Server 2005?!
sweetbuttercupAuthor Commented:
I'm using MS-SQL 2005.

 I changed the dbowner sucessfully.

Now the problem is with the tables

When I run

sp_changeobjectowner table1' , 'newowner'

I get
Database principal or schema 'newowner' does not exist in this database.

How do I change the ownership of the 100 tables at once.. Sorry for asking too much details but My SQL knowledge is very limited

Aneesh RetnakaranDatabase AdministratorCommented:
> sp_changeobjectowner table1' , 'newowner'

Make sure that 'newowner' exists in the database

use the following to add the user ifnot exists

exec sp_addLogin 'newOwner'

exec sp_addUser 'newOwner'

refer books online for more details

and run the following script to change the ownership of all the tables

declare @ObjectName varchar(256)
-- we are only interested in USER Objects
-- not already owned by 'sa'
-- we don't want keys and constrainst
set @ObjectName = (
select top 1 [name] from sysobjects
where uid <> SUSER_SID('sa')
and [type] = 'U'
declare @ObjectOwner varchar(256)
declare @ObjectFullName varchar(512)
declare @NewOwner varchar(256)
set @NewOwner = 'dbo' -------------------------change dbo to new owner name

-- default to 'dbo' if null
set @NewOwner = isnull(@NewOwner, 'dbo')

while @ObjectName is not null
    select @ObjectOwner = USER_NAME(uid)
    from sysobjects where [name] = @ObjectName
    set @ObjectFullName = @ObjectOwner + '.' + @Objectname
    PRINT 'Changing ownership of ''' + @Objectname +
    ''' from ''' + @ObjectOwner + ''' to ''' +
    @NewOwner + ''''
    execute sp_changeobjectowner @ObjectFullName, @NewOwner
    set @ObjectName = (select top 1 [name] from sysobjects
    where uid <> SUSER_SID('sa')
    and [type]  = 'U')

sweetbuttercupAuthor Commented:

When I run the long script above I get "The command(s) completed successfully." but it doen't change anything.
If I try
exec sp_addLogin 'newOwner'

exec sp_addUser 'newOwner'

Server: Msg 15025, Level 16, State 1, Line 1
The server principal 'newOwner' already exists.
Server: Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.
If I run
sp_changeobjectowner table1' , 'newowner'

I get
Server: Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107
Database principal or schema 'newowner' does not exist in this database.

Aneesh RetnakaranDatabase AdministratorCommented:
run sp_helplogin for this login

exec sp_helplogin  'newowner'
sweetbuttercupAuthor Commented:

This is what I get:

LoginName= 'sweet'
SID =   0x0802C0D9DF9CFE145B9CFE088BCF5591  
DefDBName= Miramar
DefLangName= us_english

username  DBname    UserName UserOrAlias
sweet       Miramar      db_owner      MemberOf      
sweet       Miramar      dbo      User                
sweetbuttercupAuthor Commented:
Thanks a lot for your help. I didn't have to change the ownership of the tables in order to give control and security to a specific user. I kept the tables as dbo and changed the allowed database users and it solved the problem.  I was able to change the ownership of the db but didn’t have to change the tables’ ownership as I thought it had to be.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.