-1

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]

enter image description here

Matt
  • 3,305
  • 11
  • 54
  • 98

1 Answers1

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],
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • This works on changing the authorization but the (owner_sid IS NULL OR SUSER_SNAME(owner_sid) != N'sa') of the Where statement is failing. The databases in question seems to have a previous owner since they're restored from a different server so the owner_sid is not null and the SUser_SNAME = sa, but if you go to the properties of the database in SQL server and go to the files tab then the owner field is blank (I've updated the question to show what I mean). Any ideas? – Matt Sep 01 '21 at 00:01
  • Running that query and it will return "0x01", but if I right click on that db and go to properties -> files -> the owner field is blank – Matt Sep 01 '21 at 04:07
  • That's still not working. The owner_sid for this db is still "0x01" so it doesn't match your query. Im not sure whether this is because it has been backed up on one server and restored to another server and it drops the link in SQL, since the owner_sid is "0x01" but in the properties of the DB -> Files tab the owner is still blank – Matt Sep 05 '21 at 22:43