CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
veljasije
veljasije

Reputation: 7092

database owner is empty after restoring database?

I have backup from production database, which I restore on my local computer.

But when I try to create diagram, I have got message where is noticed problem with authorization. Ok, I went to change database owner, right click on database, option Files, and I have noticed that owner field is empty.

Why is it empty?

Upvotes: 0

Views: 2380

Answers (2)

Peter Jarrett
Peter Jarrett

Reputation: 125

I had this issue then realised that it would be affecting quite a few databases so i needed some way of finding which databases were affected - using the latest SQL SSMS 17 I found that if I was the person who had restored the database that the usual suggestions of sp_helpdb etc. don't work as they fill in the owner name with your own username, yet the "owner" field is still empty in the files tab of the database properties.

I used the SQL Profiler and found that it uses:

use MyDatabaseThatsMissingItsOwner;
select suser_sname((select sid from sys.database_principals where name = N'dbo'));

to populate that field, and bingo, that will indeed return a null string if the originally owner is missing.

Upvotes: 1

steoleary
steoleary

Reputation: 9298

This is most likely because the login that was set as the owner on the production server doesn't exist on the server you restored it to.

You can recreate this by creating a login, say "test_user", creating a database and making "test_user" the owner.

Backup the database, delete it, then delete the "test_user" login.

Restore the database you deleted, the owner will now be blank.

Upvotes: 2

Related Questions