I am getting the following 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.
I read about ALTER AUTHORIZATION, but I have no idea what database this is happening in. This error is getting spit out very frequently, and grows the error log by about 1GB every day.
This question is related to
sql-server-2008
The answer is
As the message said, you should set permission as owner to your user. So you can use following:
ALTER AUTHORIZATION
ON DATABASE::[YourDBName]
TO [UserLogin];
Hope helpful! Leave comment if it's ok for you.
another way of doing it
ALTER AUTHORIZATION
ON DATABASE::[DatabaseName]
TO [A Suitable Login];
This may also happen when the database is a restore from a different SQL server or instance. In that case, the security principal 'dbo' in the database is not the same as the security principal on the SQL server on which the db was restored. Don't ask me how I know this...
Under Security, add the principal as a "SQL user without login", make it own the schema with the same name as the principal and then in Membership make it db_owner.
Do Graphically.
Database right click-->properties-->files-->select database owner-->select [sa]-- ok
Go to the Properties - Files. The owner name must be blank. Just put "sa" in the user name and the issue will be resolved.
In my case I got this error when trying to impersonate as another user. E.g.
EXEC AS USER = 'dbo';
And as the database was imported from another environment, some of its users did not match the SQL Server logins.
You can check if you have the same problem by running the (deprecated) sp_change_users_login (in "Report" mode), or use the following query:
select p.name,p.sid "sid in DB", (select serp.sid from sys.server_principals serp where serp.name = p.name) "sid in server"
from sys.database_principals p
where p.type in ('G','S','U')
and p.authentication_type = 1
and p.sid not in (select sid from sys.server_principals)
If in that list shows the user you are trying to impersonate, then you probably can fix it by assigning the DB user to the proper login in your server. For instance:
ALTER USER dbo WITH LOGIN = dbo;
Also had this error when accidentally fed a database connection string to the readonly mirror - not the primary database in a HA setup.
After restoring a Database from SQL2016 to SQL2019, I had the same issue when I try to access Database Diagrams. I had the correct Database owner already but owner of Files was empty. Once I set that, it worked properly...
Selected answer and some others are all good. I just want give a more SQL pure explanation. It comes to same solution that there is no (valid) database owner.
Database owner account dbo which is mentioned in error is always created with database. So it seems strange that it doesn't exist but you can check with two selects (or one but let's keep it simple).
SELECT [name],[sid]
FROM [DB_NAME].[sys].[database_principals]
WHERE [name] = 'dbo'
which shows SID of dbo user in DB_NAME database and
SELECT [name],[sid]
FROM [sys].[syslogins]
to show all logins (and their SIDs) for this SQL server instance. Notice it didn't write any db_name prefix, that's because every database has same information in that view.
So in case of error above there will not be login with SID that is assigned to database dbo user.
As explained above that usually happens when restoring database from another computer (where database and dbo user were created by different login). And you can fix it by changing ownership to existing login.
If the above does not work then try the following. It solved the problem for me even when the owner was well defined for the database.
SQL Server 2008 replication failing with: process could not execute 'sp_replcmds'
USE [<dbname>]
GO
sp_changedbowner '<user>' -- you can use 'sa' as a quick fix in databases with SQL authentication
Source: Stackoverflow.com