T-SQL to change owner to current user if database has no owner

Question

Just wondering if there is some T-SQL that will allow me to assign a user as the owner of a SQL Server database if it doesn't already have an owner?

We're getting the following error out in customer setups:

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission

This is because the database doesn't have an assigned owner. So we want to get a script to integrate into the product that will check if there is an assigned owner and if not run something like:

EXEC sp_changedbowner [current]

Answer 1

You should be changing the owner to 'sa', rather than the current user .

This will generate the change TSQL to do what you want:

SELECT
    sqltorun = 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO [sa];'
FROM    
    sys.databases d
WHERE
   (
    owner_sid IS NULL 
    OR SUSER_SNAME(owner_sid) != N'sa'
    OR owner_sid IN 
       (
          select sid
          from sys.database_principals 
          where type in ('G','S','U')
          and sid not in (select sid from sys.server_principals)
          and name not in ('guest')
       )
   )
   AND name IN ('Mydbname1', 'mydbname2');

You will need to execute the output of this as dynamic SQL.

Note: might need to use QUOTE_NAME() on the database name.

This is how the SSMS database properties form retrieves the database owner:

SELECT ISNULL(suser_sname(dtb.owner_sid),'''') AS [Owner],