Solved

Change Database Owner

Posted on 2013-11-07
5
388 Views
Last Modified: 2013-11-13
I recently upgraded from SQL Server 2000 to SQL Server 2008.  To do this, I created new databases in 2008, then restored the 2000 bak file to the 2008 database.  All seems to be working just fine for about a week now.  

I need to change the length of a varchar field from 50 to 200 characters.  Should be simple enough.  However, I'm getting a message stating that the change can't be made because there is no owner assigned to the database.  I've tried changing the owner using:

exec sp_changedbowner 'netadmin'

I am getting this message:
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

It doesn't make sense to me that I can only assign a database owner that does NOT exist.  What am I missing here?

Thanks,

Linda
0
Comment
Question by:VMKuser
5 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39632266
try recreating the user

USE MyDB
GO
SP_DROPUSER ‘test_user’ – Dropping test_user from the user database
GO
SP_CHANGEDBOWNER ‘test_user’ – Changing the OWNER of the database

Check this Kb article
http://support.microsoft.com/kb/866052
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39632375
Did you re-sync any/all "orphaned" users in that db?

In particular, we need to make sure that user 'netadmin' is not orphaned:

USE <your_db_name>
EXEC sp_change_users_login 'UPDATE_ONE', 'netadmin', 'netadmin'

Then see if it's now accurately shown as the (already existing) owner:

EXEC sp_helpuser
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39632391
Change the owner to sa like this:

ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa
0
 

Author Comment

by:VMKuser
ID: 39632636
None of these worked when I was trying to change the dbowner to 'netadmin', an administrator account.  But when I tried changing to 'sa', zberteoc's suggestion worked.  So I guess 'sa' will remain the owner of the db.  Works for me.

Thanks, everyone.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39633477
Another thing you can do is to use the UI from Management Studio. You should have MS 2008 after upgrade. To change the owner:

1. Right click on the database name
2. Click Properties
3. On left panel choose Files
4. In the right panel you can change the Owner by either typing the whole name if you know it or you can search it on your domain if you click on ...(domain users)

At this point you can apply the changes in 2 ways:

1. Click OK
2. Click on the Script icon in the top left corner, which will generate the SQL script in a new query window and you can run it from there.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL profiler equivalent in MS-Access 3 57
SQL Server - SQL field is defined as text 3 36
SSRS 2013 - Overlapping reports 2 19
SQL Server 2012 r2 - Sum totals 2 22
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now