User dbo after recover

Question

Hi,

I've got a problem. I backed  up my old SQL Server and recovered it on a new one. Now I've got the problem that the user dbo is broken. When I open the prefrences of the user dbo there's no login name. When I click OK I get an error that there's no login name, but I can't add an user. What to do now, can someone help?

THX

Answer 1

Hi JC

To change the owner of the database, you need to run the following

ALTER AUTHORIZATION
 ON DATABASE::databasename
TO sqlserverusername

http://msdn.microsoft.com/en-us/library/ms187359.aspx

Map to sa, or to the service account, or to another user account. This maps the user account to dbo

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 2

Hi Ewan,

well, I tried this before, the system says that the new database owner is already a user or alias of the database. When I open the prefences of the user dbo in a database
it lools like that:

http://img245.imageshack.us/img245/7324/unbenanntqn.jpg

When I click OK then I get an error; there's no login name, that's the problem. I tried to create a new database and export the old one in the new, this run rather good, but all primary keys get lost. What can I do?

Thanks a lot!!

Answer 3

Hi JC

You just have to use a login that doesn't exist as a user in the database already.

If you do require a specific login to be the owner, you can drop them (delete from the database -> users folder), and then run the ALTER AUTHORIZATION command. This is provided the user doesn't own objects in the database already.

Usually, you can change the owner to sa without issue, since sa is not commonly added as a specific database user.

ALTER AUTHORIZATION
 ON DATABASE::databasename
TO sa

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 4

Hi Ewan,

it works, thanks a lot!!

THX

Answer 5

Hi JC

If your question has been answered, can you mark any helpful posts and any posts that have answered your question. It helps others searching for the same issue in the future to find the answer, and keeps the forums tidy.

Thanks

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.