[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS-SQL tables ownership

Posted on 2006-04-22
11
Medium Priority
?
1,302 Views
Last Modified: 2007-12-19

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?
0
Comment
Question by:sweetbuttercup
  • 5
  • 4
  • 2
11 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 16516135
to change the ownership of the database, use the sp_changedbowner  procedure
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_30s2.asp?frame=true
to change the ownership of an object inside the database, use the sp_changeobjectowner procedure
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_1lpu.asp?frame=true
to make that for all the objects, you can query the sysobjects system table, however, ensure that you don't change the system objects...
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 16516140
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT @old = 'oldOwner_CHANGE_THIS' ,
@new = 'dbo' ,
@sql = ' IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?'' AND TABLE_SCHEMA = ''' + @old + ''' )

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

where a.ROUTINE_TYPE = 'PROCEDURE' AND a.SPECIFIC_SCHEMA = @oldOwner ANDOBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 16516204
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16516227
sweetbuttercup,
> 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



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16516232
please confirm that you are using SQL Server 2005?!
0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 16516262
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



0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16516279
sweetbuttercup,
> 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
begin
    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')
end

0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 16516382
aneeshattingal

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.




0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16523547
run sp_helplogin for this login


exec sp_helplogin  'newowner'
0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 16526983
aneeshattingal,

This is what I get:

LoginName= 'sweet'
SID =   0x0802C0D9DF9CFE145B9CFE088BCF5591  
DefDBName= Miramar
DefLangName= us_english
Auser=yes
Aremore=no      

username  DBname    UserName UserOrAlias
==========================
sweet       Miramar      db_owner      MemberOf      
sweet       Miramar      dbo      User                
0
 
LVL 1

Author Comment

by:sweetbuttercup
ID: 16579970
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question