Solution for Error "The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'"

Before we are going to solve the problem, we need to know how it happened.
Next time we don't repeat same mistake.
I will explain with following example::
I login to my sql server instance with sqlauthentication login called  "sql_login"
I created database name called "Owner_test", created table with two different Sps
and check the Owner SID in master database and its own database.

USE
GO
/****** Object: Database [Owner_test] Script Date: 01/03/2012 14:30:05 ******
/CREATE DATABASE [Owner_test] ON PRIMARY ( NAME = N'Owner_test', FILENAME = N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\Owner_test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON ( NAME = N'Owner_test_log', FILENAME = N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\Owner_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO-
----create table and insert data
USE Owner_test
Go
Create table Test(id int)
Go
insert into Test select 1 union all select 2 union all select 3
---create two stoted procs
--1)create sp and it will execute with its callerID
create procedure Test_proc1
as begin
select * from Test
END

GO

---2) )create sp and it will Execue with other user
---When you create this type of stored procedure, change database property trustworthy to ON.
ALTER DATABASE [Owner_test] SET TRUSTWORTHY ON GO

select SUSER_SNAME(owner_sid) FROM sys.databases WHERE name='Owner_test'

Here it giving sql_login

--To get the owner SID recorded for the current database owner

SELECT SUSER_SNAME(sid) FROM Owner_test.sys.database_principals WHERE name=N'dbo'

Here it giving sql_login

After successfully designed this database in DEV, now its time to move this database to UAT or PRD.
We can move this database to other server by detach, copy and atach to other server or take backup of database, copy to other server and restore it.
In UAT, I login into server with windows authentication called "'Domain\Name'"
I attched "Owner_test" database into UAT server.


----Now Execute both stored procs in UAT server
EXEC Test_proc1---successfully executed
EXEC Test_proc2---This stored proc given below error.
Msg 916
The , Level 14, State 1, Procedure Test_proc2, Line 0server principal "'Domain\Name'" is not able to access the database "Owner_test" under the current security context.
The problem is Owner ISID in master database and its own database are diferent
--To get ownerSID  from master database
select SUSER_SNAME(owner_sid) FROM sys.databases WHERE name='Owner_test'

Here it giving
'Domain\Name'

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

Here it giving sql_login

Here is the solution fo this problem.
ALTER AUTHORIZATION ON Database::Owner_test TO [Domain\Name]
Now OwnerISID in both places are same and sps will execute sucessfully.


Here is the Automation script, it will check above descrepency in all databases and it will fix OwnserISID problem.

IF OBJECT_ID(N'tempdb..#owners', 'U') IS NOT NULL

DROP TABLE #owners;

CREATE TABLE #owners

(

database_name sysname NOT NULL,

sys_databases_sid varbinary(85) NOT NULL,

sys_databases_owner nvarchar(256) NULL,

sys_users_sid varbinary(85) NULL,

sys_users_owner nvarchar(256) NULL

);

INSERT INTO #owners

(

database_name,

sys_databases_sid,

sys_databases_owner

)

SELECT

name,

owner_sid,

SUSER_SNAME(owner_sid)

FROM sys.databases;

---select * from #owners     

EXEC sp_MSforeachdb 'UPDATE #owners SET sys_users_sid =(SELECT sid

FROM [?].sys.database_principals WHERE name = ''dbo''),      sys_users_owner = (

SELECT SUSER_SNAME(sid)

FROM [?].sys.database_principals

WHERE name = ''dbo'')

WHERE database_name = ''?''

';

SELECT * FROM #owners

WHERE sys_databases_sid <> sys_users_sid OR sys_databases_owner IS NULL;

DECLARE @database_name varchar(100),@owner varchar(100)

DECLARE C CURSOR FOR SELECT database_name, Sys_databases_owner FROM #owners

WHERE  sys_databases_sid <> sys_users_sid     OR sys_databases_owner IS NULL

OPEN C

FETCH NEXT FROM C INTO @database_name,@owner

WHILE @@FETCH_STATUS=0

BEGIN

raiserror('procesing database=%s',0,1,@database_name) with nowait

Print 'ALTER AUTHORIZATION ON DATABASE:: '+@database_name+' to ['+@owner+'] ;'

EXEC ('ALTER AUTHORIZATION ON DATABASE:: '+@database_name+' to ['+@owner+'] ;')

FETCH NEXT FROM C INTO @database_name,@owner

END

CLOSE C

DEALLOCATE C