SQL 澄清为什么 EXECUTE AS USER/LOGIN 没有返回预期的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13037863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Clarification as to why EXECUTE AS USER/LOGIN is not returning the expected results?
提问by athom
I am running the following query against a database:
我正在对数据库运行以下查询：
execute as user = 'domain\username'
select * from fn_my_permissions(null, 'DATABASE')
order by subentity_name, permission_name
revert;
But the following error gets thrown:
但是会抛出以下错误：
Cannot execute as the database principal because the principal "dev\spadmin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Cannot execute as the database principal because the principal "dev\spadmin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
The user is the dbo of the database, and when I open up the properties in management studio, I can see that it is associated with that login. Running EXECUTE AS LOGIN = 'domain\username'does return results, on the other hand. And if I explicitly run EXECUTE AS USER = 'dbo', I get results. I also have a different database where this same scenario returns results with both EXECUTE AS USERand EXECUTE AS LOGIN.
用户是数据库的 dbo，当我在 management studio 中打开属性时，我可以看到它与该登录名相关联。EXECUTE AS LOGIN = 'domain\username'另一方面，运行确实会返回结果。如果我明确运行EXECUTE AS USER = 'dbo'，我会得到结果。我还有一个不同的数据库，其中相同的场景返回结果与EXECUTE AS USER和EXECUTE AS LOGIN。
In another scenario with a different user, I have ran EXECUTE AS LOGIN = 'domain\username'and I do not get results, but I do get results with EXECUTE AS USER = 'domain\username'.
在另一个使用不同用户的场景中，我运行了EXECUTE AS LOGIN = 'domain\username'但没有得到结果，但我确实得到了EXECUTE AS USER = 'domain\username'.
Both users in these scenarios are associated with logins that are members of db_ownerfor the database.
这些场景中的两个用户都与db_owner作为数据库成员的登录名相关联。
Can anybody tell me why these queries are not returning the results that I expect? And let me know if I am missing any important information. Thanks!
谁能告诉我为什么这些查询没有返回我期望的结果？如果我遗漏了任何重要信息，请告诉我。谢谢！
采纳答案by RBarryYoung
The problem is that because the Login domain\usernameis the dbo of the database, that alsomeans that the name of their corresponding User within that database is dboand notdomain\username.
问题是因为 Logindomain\username是数据库的 dbo，这也意味着他们在该数据库中对应的 User 的名称是dbo而不是domain\username。
回答by Remus Rusanu
run ALTER AUTHORIZATION ON DATABASE::[<yourdb>] TO [sa]
跑 ALTER AUTHORIZATION ON DATABASE::[<yourdb>] TO [sa]
回答by Jim Miller
I had the same error for a stored procedure that I wrote.
对于我编写的存储过程，我遇到了同样的错误。
I found the error was caused by the way I had specified the database name in the query
我发现错误是由我在查询中指定数据库名称的方式引起的
SELECT emp_no
FROM db_name.employee
WHERE emp_no = 1234
I was executing the procedure on db_name2once I removed the database name from my script
db_name2从脚本中删除数据库名称后，我正在执行该过程
SELECT emp_no
FROM employee
WHERE emp_no = 1234
it worked fine.
它工作正常。
I don't think the reduced access rights of the log allow for the use of other databases or the use db_namecommand.
我认为减少的日志访问权限不允许使用其他数据库或use db_name命令。
