Quick Scan Report – No Database Owner

What this check looks for

Databases where SUSER_SNAME(owner_sid) returns null, meaning the SID recorded as the owner does not resolve to any login on this instance.

Why it matters

The database still works, which is why this survives for years, and then a handful of specific things fail in ways that never mention ownership.

The usual cause is ordinary: the database was created by a person, that person left, and their Windows account was deleted or their login was dropped. The SID stays recorded in sys.databases and resolves to nothing.

What breaks:

  • Database Mail and Agent job ownership. Jobs owned by a login that no longer exists fail with permission errors that name the job, not the ownership.
  • Cross database ownership chaining and TRUSTWORTHY. Both depend on the owner, and both fail or behave unexpectedly when it does not resolve.
  • Change data capture, Service Broker, and some replication operations, all of which check the database owner.
  • dbo becomes unreliable. The dbo user in the database maps to the owner SID. With no matching login, nothing maps to dbo, which produces confusing permission behaviour for anyone expecting to inherit it.
  • Restores and attaches. Moving the database to another instance carries the unresolvable SID with it, so the problem follows the database rather than staying with the server.

None of these produce an error mentioning the database owner. That is what makes this worth fixing before you need it rather than during the incident it causes.

How to confirm it yourself

SELECT [name]                       AS [database_name],
       SUSER_SNAME([owner_sid])     AS [owner],
       [owner_sid],
       [state_desc],
       [is_trustworthy_on],
       [is_db_chaining_on],
       [create_date]
  FROM sys.databases WITH (NOLOCK)
 ORDER BY CASE WHEN SUSER_SNAME([owner_sid]) IS NULL THEN 0 ELSE 1 END, [name];

Agent jobs with the same problem, which usually appear together:

SELECT j.[name]                     AS [job_name],
       SUSER_SNAME(j.[owner_sid])   AS [owner],
       j.[enabled]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 WHERE SUSER_SNAME(j.[owner_sid]) IS NULL
 ORDER BY j.[name];

How to fix it

One statement per database:

USE [YourDatabase];
GO
ALTER AUTHORIZATION ON DATABASE::[YourDatabase] TO [sa];

ALTER AUTHORIZATION replaces the deprecated sp_changedbowner, which still works but should not be used in anything new.

Choose the owner deliberately. sa is the conventional answer and it is a reasonable default, because it always exists, it is never deleted when somebody leaves, and it does not grant anything the database did not already have. Two notes:

  • If sa is disabled or renamed, which is a good practice in its own right, it is still a valid owner. A disabled login can own a database.
  • If the database has TRUSTWORTHY ON, think before setting the owner to a sysadmin. That combination lets code inside the database act with sysadmin rights on the instance, which is a well known privilege escalation path. Either turn TRUSTWORTHY off, which is usually correct, or choose a low privileged owner.
-- check before you choose
SELECT [name], [is_trustworthy_on], SUSER_SNAME([owner_sid]) AS [owner]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_trustworthy_on] = 1;

Fix the Agent jobs at the same time, since they usually share the cause:

EXEC msdb.dbo.sp_update_job @job_name = N'YourJobName', @owner_login_name = N'sa';

Then stop it recurring. Databases owned by a named person are a leaving-date problem waiting to happen. Setting ownership to sa at creation time, as a standard, removes the whole category.

How long it takes

About two hours across an instance, most of it checking TRUSTWORTHY and deciding the right owner rather than running the statements.

Report Why you would go there
Database Overview Owner and settings for every database.
Security Posture Ownership alongside the rest of the security surface.
Logins Which logins exist to own things.
Failed Jobs Jobs failing because their owner does not resolve either.
Agent Security Job ownership and the accounts steps run as.
Orphan Users The database user equivalent of the same disappearance.
Check
Database ownership Ownership that resolves but is not what you would choose.
Orphan database users Users left behind by the same deleted login.
Jobs without failure notification Why the resulting job failures were not noticed.
SQL logins with blank passwords or policy turned off The rest of the login hygiene picture.

Frequently asked questions

Everything works. Is this urgent? Not urgent, which is why it is Medium. It is a latent fault that surfaces when you enable a feature, restore the database elsewhere, or investigate an unrelated permission error.

Should the owner be sa? Usually yes. It always exists and it survives people leaving. The exception is a TRUSTWORTHY database, where a sysadmin owner is a privilege escalation path.

Can I set the owner while users are connected? Yes. ALTER AUTHORIZATION is a metadata change and does not need exclusive access.

Does this affect backups? The backup works. The ownership travels with the database, so restoring it onto another instance reproduces the problem there.