VMKuser
asked on
Change Database Owner
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks, everyone.
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.
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.
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