Clarification as to why EXECUTE AS USER/LOGIN is not returning the expected results?
sql, sql-server, sql-server-2008, sql-server-2008-r2, ssms
Solution
The problem is that because the Login `domain\username` is the dbo of the database, that also means that the name of their corresponding User within that database is `dbo` and not `domain\username`.
Problem
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.` 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 USER` and `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'`. Both users in these scenarios are associated with logins that are members of `db_owner` for the database. 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!