Failed assembly deploy due to non-matching SIDs

Question

Database names and logins are anonymized in what follows. There are some answers on SO that are similar to this situation, but not exactly the same, hence my question.

Attempting to deploy an assembly to production database FOO_PROD fails with message:

Msg 33009, Level 16, State 2, Line 17
The database owner SID recorded in the master database differs from the
database owner SID recorded in database 'FOO_PROD'. You should correct
this situation by resetting the owner of database 'FOO_PROD' using the
ALTER AUTHORIZATION statement.

Indeed, the following two queries demonstrate the difference in SID. First, we look at the SID of FOO_PROD:

SELECT  SD.[SID],
        SL.Name as [LoginName]
FROM  master..sysdatabases SD INNER JOIN master..syslogins SL
    on  SD.SID = SL.SID
WHERE  SD.Name = 'FOO_PROD'

which shows the result of:

SID,                                                             LoginName

0x010500000000000515000000C4B7E63D99D15C20332A47A24B100000,      BATZ\boink

Second, we look at the SID of FOO_PROD in the master database:

SELECT  SD.[SID],
        SL.Name as [LoginName]
FROM  master..sysdatabases SD INNER JOIN master..syslogins SL
    on  SD.SID = SL.SID
WHERE  SD.Name = 'master'

which shows the result of:

SID,      LoginName

0x01,   [sa]

We notice that indeed, just as Visual Studio complained, the SIDs do not match. They must be made to match in order to proceed (apparently).

Constraints: The SID on FOO_PROD cannot be changed because several other systems that use the database expect it to have the SID and LoginName it currently has.

Question 1: Is the solution then to change the SID, LoginName on the master database? Would it hurt anything or be a bad idea to do so?

Say you respond that it is ok to change the SID, LoginName on master, then, how does one make the change to the 'master' database? Well, I've not done it before, but candidate solutions and commentary can be found here: The database owner SID recorded in the master database differs from the database owner SID

However, this situation is different from those presented in the link above, I think, in that the change must happen to/on the master database ala:

  exec sp_changedbowner [BATZ\boink]

Question 2: Is that the correct way to do it?

Naturally I'll check with stakeholders if such a change to master database will cause undesired outcomes, but I hope to get some guidance here before I even check on that.

Update based on @srutzky's updated answer:

  -- Step 1
  SELECT sd.[name], sd.[owner_sid], sp.[name]
  FROM   sys.databases sd
  INNER JOIN sys.server_principals sp
          ON sp.[sid] = sd.[owner_sid]
  WHERE  sd.[name] = N'FOO_PROD';

returns:

  name, owner_sid,  name
  FOO_PROD, 0x010500000000000515000000C4B7E63D99D15C20332A47A24B100000, BATZ\boink

Then

  -- Step 2
  USE [FOO_PROD];
  SELECT dp.[sid], sp.[name]
  FROM   sys.database_principals dp
  INNER JOIN sys.server_principals sp
          ON sp.[sid] = dp.[sid]
  WHERE  dp.[name] = N'dbo';

returns:

  sid,  name
  0x01, sa

Answer 1

Yes, the SIDs really do need to match as a mismatch is an indication of a potentially harmful DB being restored to the instance; this is a safe-guard.

BUT, first we need to know exactly what we are looking at. While there is definitely a mismatch in the owner SIDs between the record in FOO_PROD and the record in master (hence the error message), your queries are not looking at the value in FOO_PROD. Your two queries are looking at the value in master for the owner of FOO_PROD, and in master (again) for the owner of master (entirely irrelevant here), respectively.

Step 1

Do not use sys* objects for anything as those are compatibility Views so that older stuff written for SQL Server 2000 and prior still work (well, dbo.sys* tables in msdb are still valid). Starting with SQL Server 2005, only sys.* objects (no need to specify master.) should be used. Meaning, use:

SELECT sd.[name], sd.[owner_sid], sp.[name]
FROM   sys.databases sd
INNER JOIN sys.server_principals sp
        ON sp.[sid] = sd.[owner_sid]
WHERE  sd.[name] = N'FOO_PROD';

Step 2

You need to check the value IN the database itself for the owner's SID as it has it recorded, which is not in sys.databases (or even in master..sysdatabases). When checking the Database's value for it's owner, you need to look in sys.database_principals for the dbo User as follows:

USE [FOO_PROD];
SELECT dp.[sid], sp.[name]
FROM   sys.database_principals dp
INNER JOIN sys.server_principals sp
        ON sp.[sid] = dp.[sid]
WHERE  dp.[name] = N'dbo';

Step 3

Using sp_changedbowner is required if you are on SQL Server 2005, but starting with SQL Server 2008 that stored procedure is deprecated in favor of the newer ALTER AUTHORIZATION (though it still works). But yes, this is the way to make them the same as it will sync both locations to whichever Login you specify.

However, you need to make sure that BATZ\boink is a valid Windows Login for the domain that the SQL Server instance belongs to, AND that this particular Windows Login has an SID of 0x010500000000000515000000C4B7E63D99D15C20332A47A24B100000. If the Login does not exist, hopefully you will be able to create it via CREATE LOGIN.

Answer 2

Since the owner in the database is SA, and you want to change the owner recorded in Master to SA, just run

alter authorization on database::[foo_prod] to sa