SQL Server principal dbo does not exist

Question

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

User · Answer

another way of doing it  ALTER AUTHORIZATION  ON DATABASE   DatabaseName  TO  A Suitable Login

User · Answer

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

User · Answer

Also had this error when accidentally fed a database connection string to the readonly mirror - not the primary database in a HA setup

User · Answer

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

User · Answer

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  quot Report quot  mode   or use the following query  select p name p sid  quot sid in DB quot    select serp sid from sys server principals serp where serp name   p name   quot sid in server quot  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

User · Answer

USE   lt dbname gt   GO sp changedbowner   lt user gt   -- you can use  sa  as a quick fix in databases with SQL authentication   KB913423 - You cannot run a statement or a module that includes the EXECUTE AS clause after you restore a database in SQL Server 2005

User · Answer

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

User · Answer

I resolved this issue by setting database owner  My database did not have had any owner before this issue  Execute this command in your database to set owner to sysadmin account   use  YourDatabaseName  EXEC sp changedbowner  sa

User · Answer

Go to the Properties - Files  The owner name must be blank  Just put  quot sa quot  in the user name and the issue will be resolved

User · Answer

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

User · Answer

Do Graphically   Database right click-- properties-- files-- select database owner-- select  sa -- ok

User · Answer

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

User · Answer

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   39 sp replcmds  39

[sql-server-2008] SQL Server principal "dbo" does not exist,

Examples related to sql-server-2008