1

This question is focussed on why this has happened.

We restored a database from another server that has failed. The restore succeeded, but when we try and run any stored procedure (using 'sa' login, or Windows-based login with full admin access), we got this error:

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.

Lots of SO users have had the same issue, so I followed the related answers. For example, both of the following snippets:

EXEC sp_changedbowner 'dbo';
ALTER AUTHORIZATION ON DATABASE::MyDb TO dbo;

...result in this:

Cannot find the principal 'dbo', because it does not exist or you do not have permission.

On the previous server (and my restoration dev server) there is no dbo user to reassociate as the owner. I'm not sure how to locate the principal, but it is listed in the Schema.

  1. What causes 'dbo' to not be found, or what has happened to the dbo principal?
  2. Can it be reinstated/recreated?
  3. Does dbo have to link to an actual login account?
Dale K
  • 25,246
  • 15
  • 42
  • 71
EvilDr
  • 8,943
  • 14
  • 73
  • 133
  • Has anybody ever made `ALTER AUTHORIZATION ON DATABASE::MyDb TO dbo` work? The target principal needs to be a Login as opposed to a (Database) User. Same goes for `sp_changedbowner`. – AlwaysLearning Apr 29 '21 at 09:32
  • There's an answer on SO with 500 upvotes, so I guess so. Didn't work here though! – EvilDr Apr 29 '21 at 10:29
  • For `alter authorization` generally, perhaps, but [alter authorization on database](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql#alter-authorization-for-databases) cannot work using database principals - that would lead to a paradox and destroy the universe. See also [sp_changedbowner](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql). – AlwaysLearning Apr 29 '21 at 10:37

1 Answers1

1

The database owner is a server-level principal (i.e. login) or Windows user (not necessarily with a login). As noted in the comments, dbo is a database principal rather than a server principal (assuming you don't happen to have a login named dbo).

The owner is stored in the master database and visible as the owner_sid column of the sys.databases catalog view:

SELECT name AS DatabaseName, owner_sid, SUSER_SNAME(owner_sid) AS OwnerName
FROM sys.databases
WHERE name = N'MyDb';

The database owner is also stored in the database itself as the well-known dbo user:

SELECT SUSER_SNAME(sid) AS OwnerName, sid
FROM sys.database_principals AS dp
WHERE name = N'dbo';

When a database is restored and it doesn't already exist, the owner (authorization) is initially set to the person who restored the database. However, the dbo entry in the database itself remains unchanged. This results in a mismatch between the owner in sys.databases and the sys.database_principals dbo user. It is necessary to execute ALTER AUTHORIZATION ON DATABASE with the desired owner after a restore to correct the mismatch.

Below is a script that demonstrates the issue when the individual executing the restore is not logged in as sa.

CREATE DATABASE MyDb; --database is owned by current login

ALTER AUTHORIZATION ON DATABASE::MyDb TO sa; --sa is an example; can be any login

--shows owners are same (sa)
SELECT name AS DatabaseName, owner_sid, SUSER_SNAME(owner_sid) AS OwnerName
FROM sys.databases
WHERE name = N'MyDb';

SELECT SUSER_SNAME(sid) AS OwnerName, sid
FROM MyDb.sys.database_principals AS dp
WHERE name = N'dbo';

BACKUP DATABASE MyDb TO DISK=N'C:\Backups\MyDb.bak' WITH INIT;

DROP DATABASE MyDb;

RESTORE DATABASE MyDb FROM DISK=N'C:\Backups\MyDb.bak';

--shows owners are different (current user and sa)
SELECT name AS DatabaseName, owner_sid, SUSER_SNAME(owner_sid) AS OwnerName
FROM sys.databases
WHERE name = N'MyDb';

SELECT SUSER_SNAME(sid) AS OwnerName, sid
FROM MyDb.sys.database_principals AS dp
WHERE name = N'dbo';

ALTER AUTHORIZATION ON DATABASE::MyDb TO sa; --sa is an example; can be any login

--shows owners are same (sa)
SELECT name AS DatabaseName, owner_sid, SUSER_SNAME(owner_sid) AS OwnerName
FROM sys.databases
WHERE name = N'MyDb';

SELECT SUSER_SNAME(sid) AS OwnerName, sid
FROM MyDb.sys.database_principals AS dp
WHERE name = N'dbo';

Below is an excerpt from the documentation regarding the database owner principal.

The new owner principal must be one of the following:

A SQL Server authentication login.
A Windows authentication login representing a Windows user (not a group).
A Windows user that authenticates through a Windows authentication login representing a Windows group.
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thank you for a very clear answer. The second code sample produces now rows, so I ran this which completed successfully, `ALTER AUTHORIZATION ON DATABASE::MyDatabase TO [AzureAD\MyName];`. However, running the second code sample again still returns no rows, but in Properties my name is listed. Running a proc from the database produces, *Could not obtain information about Windows NT group/user 'AzureAD\MyName', error code 0x54b.* Why might that occur please? – EvilDr Apr 30 '21 at 09:35