blocks|key|200783|text|我通过设置数据库所有者解决了这个问题。在这个问题之前，我的数据库没有任何所有者。在数据库中执行以下命令，将所有者设置为sysadmin帐户：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|200784|use+[YourDatabaseName]+EXEC+sp_changedbowner+'sa'|code-block|syntax|javascript|200785|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>use [YourDatabaseName] EXEC sp_changedbowner 'sa'
</code></pre>

blocks|key|200980|text|​|type|unstyled|depth|inlineStyleRanges|entityRanges|data|200981|📷|atomic|offset|length|200982|200983|用图形化的方式。|200984|数据库右键单击-->属性-->文件-->选择数据库所有者-->选择sa--确定|200985|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/af18a1084a1719c1d6238bb4c7b62699.png|imageAlt^0|0|0|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@$E|W|F|X|1|Y]]|A|$]]|$1|G|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S|T|-4]]]]

<a href="https://i.stack.imgur.com/8t1P7.png" rel="noreferrer"><img src="https://i.stack.imgur.com/8t1P7.png" alt="enter image description here"></a>

Do Graphically.

Database right click-->properties-->files-->select database owner-->select [sa]-- ok

blocks|key|200811|text|USE+[<dbname>]
GO
sp_changedbowner+'<user>'+--+you+can+use+'sa'+as+a+quick+fix+in+databases+with+SQL+authentication|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|200812|KB913423+-+You+cannot+run+a+statement+or+a+module+that+includes+the+EXECUTE+AS+clause+after+you+restore+a+database+in+SQL+Server+2005|unstyled|offset|length|200813|entityMap|0|LINK|mutability|MUTABLE|url|http://support.microsoft.com/kb/913423^0|0|0|3P|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>USE [&lt;dbname&gt;]
GO
sp_changedbowner '&lt;user&gt;' -- you can use 'sa' as a quick fix in databases with SQL authentication
</code></pre>

<a href="http://support.microsoft.com/kb/913423" rel="noreferrer" title="MS - Help">KB913423 - You cannot run a statement or a module that includes the EXECUTE AS clause after you restore a database in SQL Server 2005</a>

blocks|key|419657|text|在将数据库从SQL2016恢复到SQL2019之后，我在尝试访问数据库关系图时遇到了同样的问题。我已经有了正确的数据库所有者，但文件所有者为空。一旦我设置好了，它就能正常工作了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|419658|​|419659|📷|atomic|offset|length|419660|419661|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/9a1e27754589818d6f633e5a51af3996.png|imageAlt^0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@$G|V|H|W|1|X]]|A|$]]|$1|I|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q|R|-4]]]]

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...
<a href="https://i.stack.imgur.com/fQGu9.png" rel="noreferrer"><img src="https://i.stack.imgur.com/fQGu9.png" alt="enter image description here" /></a>

blocks|key|200890|text|当从不同的SQL+server或实例恢复数据库时，也可能发生这种情况。在这种情况下，数据库中的安全主体'dbo‘与还原数据库的SQL+server上的安全主体不同。别问我是怎么知道的.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|200891|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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...

blocks|key|200999|text|另一种方式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|201000|ALTER+AUTHORIZATION+
ON+DATABASE::[DatabaseName]
TO+[A+Suitable+Login];|code-block|syntax|javascript|201001|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

another way of doing it

<pre><code>ALTER AUTHORIZATION 
ON DATABASE::[DatabaseName]
TO [A Suitable Login];
</code></pre>

blocks|key|201038|text|在Security下，将主体添加为“没有登录的SQL+user”，使其拥有与主体同名的架构，然后在Membership中将其设置为db_owner。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|201039|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|419371|text|当意外地将数据库连接字符串提供给只读镜像而不是HA设置中的主数据库时，也会出现此错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|419372|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|201123|text|正如消息所说，您应该将权限设置为用户的所有者。因此，您可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|201124|ALTER+AUTHORIZATION+
ON+DATABASE::[YourDBName]
TO+[UserLogin];|code-block|syntax|javascript|201125|希望对您有所帮助！如果你觉得合适，请留下评论。|201126|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

As the message said, you should set permission as owner to your user. So you can use following:

<pre><code>ALTER AUTHORIZATION 
ON DATABASE::[YourDBName]
TO [UserLogin];
</code></pre>

Hope helpful!
Leave comment if it's ok for you.

blocks|key|201174|text|在我的例子中，当我试图模拟另一个用户时，我得到了这个错误。例如。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|201175|EXEC+AS+USER+=+'dbo';|code-block|syntax|javascript|201176|由于该数据库是从另一个环境导入的，因此它的某些用户与SQL+Server登录名不匹配。|201177|您可以通过运行(不推荐使用的)+sp_change_users_login+(在“报告”模式下)来检查是否有相同的问题，或者使用以下查询：|offset|length|201178|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)|201179|如果在该列表中显示了您试图模拟的用户，那么您可能可以通过将DB用户分配给您的服务器中的正确登录来修复它。例如：|201180|ALTER+USER+dbo+WITH+LOGIN+=+dbo;|201181|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2016^0|0|0|0|G|L|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@$K|14|L|15|1|16]]|A|$]]|$1|M|3|N|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

In my case I got this error when trying to impersonate as another user. E.g.
<pre><code>EXEC AS USER = 'dbo';
</code></pre>
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) <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2016" rel="nofollow noreferrer">sp_change_users_login</a> (in &quot;Report&quot; mode), or use the following query:
<pre><code>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)
</code></pre>
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:
<pre><code>ALTER USER dbo WITH LOGIN = dbo;
</code></pre>

blocks|key|201349|text|转到属性-文件。所有者名称必须为空。只需在用户名中输入"sa“，问题就会得到解决。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|201350|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

I am getting the following error

<pre><code>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.
</code></pre>

I read about <code>ALTER AUTHORIZATION</code>, 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.

SQL Server principal "dbo" does not exist,

我收到以下错误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.我读过有关ALTER AUTHORIZATION的文章，但我不知

问SQL Server主体"dbo“不存在，
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server主体"dbo“不存在，EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server主体"dbo“不存在，
EN