Wednesday 30 September 2009

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Problems with database ownership SID values

I recently came across a problem which I have had a couple of times now which seems to be related to database backups in SQL Server 2005.

I was carrying out a manual backup of a database on the production server which was taking a very long time. During this process I came across another problem which needed solving quickly and I couldn't wait until the backup had finished before trying to resolve the issue which was caused by a report called from one database trying to access data from another. It was raising the following error message:

The server principal "XXXXX" is not able to access the database "XXXX" under the current security context.
The solution to this problem is to enable the database that needs to be accessed as Trustworthy with an SQL statement like this:

ALTER DATABASE strategies_jobboard SET TRUSTWORTHY ON
So I ran this statement to fix the reporting issue and I had some hesitations about doing this as I didn't know whether the combination of the backup and the lock on the Database would cause any issues and low and behold just after running this statement I started receiving the following errors:

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'. You should correct this situation by resetting the owner of database 'XXXX' using the ALTER AUTHORIZATION statement.
Now this is an error I have come across before when a database has been created on one server by restoring a backup taken from another server and the database owners differ. To prove that the problem is in fact differing SID's I ran the following two SQL statements.

--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

They should return you SID values in the format of a GUID e.g

0x010500000000000515000000BF76FC16EA335FE078A67D48FB030000

Now if the two SID's differ which they did in my case it means that you need to reset the database owner so that both values are the same. To do this you can run another ALTER statement and pass in the owner value you want to use e.g
ALTER AUTHORIZATION ON Database::XXXX TO [domain\user]


Once I had run this code the problem was fixed. Now I am pretty sure the issue was down to a combination of the backup running at the same time as the code to change the TRUSTWORTHY statement as I had no errors before that SQL was run and as soon as I had executed it the errors started shooting into my inbox.

However I tried to replicate this problem on a development server by running some backups and executing the same SQL but I couldn't re-create the problem. Maybe there was something else in the mix I was unaware of but I thought I would post the solution to the problem anyway in case others have a similar situation arise.

20 comments:

Anonymous said...

Deepest thanks and profound gratitude for posting this article. I couldn't figure out how to resolve those pesky SQL USER SIDs after migrating a database from SQL 2003 server to SQL 2008 server.

you are awesome!!!!!

Anonymous said...

Great article. It saved my life
Thanks
Thanks
Thanks again.

Kilted Viking said...

I had a database created by user sa on one server (2005), then backed up, and then restored on new server (2008) by DOMAIN\Administator.
Changing the dbo account from DOMAIN\Administrator to user sa on new server solved my problem. :-)

Unknown said...

Good job.. nice write up.

Anonymous said...

After a restore, I like make sa the owner of the database. That way I will never have this issue.

EXEC sp_changedbowner 'sa'

Anonymous said...

It would be too bad, if I use the solution and not say thank you. This is great article and it helped me big time.

THANK YOU VERY MUCH!

Anonymous said...

Thank you, this solved my problem

Anonymous said...

Perfect solution for that problem.
Thanks a lot, Great job

Anonymous said...

thank you very much. I really appreciate your help and effort putting this article.

Anonymous said...

Solved my issue when upgrading from sql 2005 to sql 2008. I had to run the following command to fix msdb ownership issue:

ALTER AUTHORIZATION ON Database::msdb TO sa

Thank you very very much. This is a great post!

Anonymous said...

THANKS!!!

Anonymous said...

This article help me to fetch the reports.Learning few commands on MSSQL.Great help

Jonas2400 said...

Great article thanks! It really saved me.

Rob Reid said...

The thing with SA as owner is that hackers know it is commonly used as the main user so they will probe for it.

Changing your user from SA to something else is a good practise for security sake I think.

Thanks

Ron

Anonymous said...

Thanks a lot. It help us to resolved the issue ASAP.

Unknown said...

Oh are my life saver right now.I have to create a job to send email for data compare and I was not able to run EXEC msdb.dbo.sp_send_dbmail because of those errors.Now ........ Hurray!!!!!!!!!!

Anonymous said...

thanks

Anonymous said...

Excellent Article, Thanks! it helped me.

Erik said...

Fixed my issue! thanks

Rob Reid said...

Glad to hear it!

This is such an annoying problem to get, and one I keep getting due to our tech guy backing up databases and restoring them when they move servers that cannot see each other.

This and re-linking orphaned users with logins that happens with a DB backup / restore are very common problems to get with this kind of DB move (backup and restore) see this article for info on re-linking orphaned users with logins.

http://blog.strictly-software.com/2010/01/update-orphaned-sql-users.html

Thanks

Rob