Posted inSQL Server

Errors 15517 or 15404 occur when opening the Database Diagrams feature in SQL Server Management Studio

August 22, 2022

Problem

Upon opening the Database Diagrams feature in SSMS, you may receive error message 15517.

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. (Microsoft SQL Server, Error: 15517)

You may also receive error message 15404

Could not obtain information about Windows NT group/user ‘user‘, error code 0x54b. (Microsoft SQL Server, Error: 15404)

Solution

Assign a new database owner. It can be any of the following:

  1. The “sa” account (this is the most common)
  2. Any standard user account if using Mixed Mode Security
  3. A Windows user account, if there is full trust relationship between the domain of the Windows account and SQL Server service account

One way to change the owner is to use ALTER ATHORIZATION T-SQL command:

ALTER AUTHORIZATION ON database::AdventureWorks2019 TO sa;

Another way is to right click on the database in SSMS and select “properties.”  The Files page under the Database Properties window will allow you to change owner.

More Information

The Database Diagrams feature tries to impersonate the user that is the DBO (Database Owner)

execute as user = N’dbo’

If the DBO inside the database is an orphaned user, you will receive error 15517.

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. (Microsoft SQL Server, Error: 15517)

If you go to the General page under Database Properties and look at the owner, you may see it is the account that restored the backup of the database.

However, if you go to the Files page you may see the Owner is blank.

How is the owner displayed in one screen but missing in the other?

The owner is recorded in two places. When a database is created, the SID (Security ID) of the creator is recorded inside the database in the sys.database_principals system view.  It is also recorded outside the database in the sys.databases system view.

If a backup of a database is restored on another SQL Server (as is the case with the Adventureworks sample database), the SID of the user/owner inside the database will not match any login, it will be an orphan. However, the account that restored the database as a new database will have a valid SID recorded in sys.databases.  The following queries show a valid owner is returned for the SID in sys.databases, but a NULL is returned for the orphaned user SID in sys.database_principals.

SELECT name as ‘Database’, owner_sid as ‘Owner sid in sys.databases’,
             SUSER_SNAME(owner_sid) as ‘OwnerName’
FROM sys.databases
WHERE name = ‘AdventureWorks2019’

SELECT name as ‘Owner’, sid as ‘Owner sid inside Adventureworks Database’,
             SUSER_SNAME(sid) as ‘OwnerName’
FROM AdventureWorks2019.sys.database_principals
WHERE name = ‘dbo’

 
Owner SID for Adventureworks in sys.databases                 OwnerName
------------------------------------------------------------  ------------
0x010500000000000515000000271A6C07352F372AAD20FA5B8EF01C00    mydomain\tim

Owner SID inside Adventureworks Database                       OwnerName
------------------------------------------------------------  -------------
0x010500000000000515000000A065CF7E784B9B5FE77C877063C23500     NULL

To fix this assign the database a valid owner. To “execute as DBO” requires the Database Engine impersonate the DBO account. If it is a Windows account and there is not a full trust relationship between the domain of the Windows account and SQL Server service account, then it will fail with error 15504.

Could not obtain information about Windows NT group/user ‘user‘, error code 0x54b. (Microsoft SQL Server, Error: 15404)

A common solution as mentioned above is to make “sa” the owner, but any of the above solutions should work.

Last updated on August 22, 2022