What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one behave unexpectedly?
Recently I've been using EXECUTE AS LOGIN to check if a certain person could or couldn't use a table function in one of our databases. The tests showed that he could but he repeatedly reported failure.
The person uses login 'WEB' to connect to the database and he has the user 'WEB' in that database associated with login 'WEB' so I tried the following scripts: -- part 1 EXECUTE AS USER = 'WEB' GO SELECT USER_NAME() AS 'user_name' ,SUSER_NAME() AS 'suser_name' ,SUSER_SNAME() AS 'suser_sname' ,SYSTEM_USER AS 'system_user' GO REVERT GO and -- part 2 EXECUTE AS LOGIN = 'WEB' GO SELECT USER_NAME() AS 'user_name' ,SUSER_NAME() AS 'suser_name' ,SUSER_SNAME() AS 'suser_sname' ,SYSTEM_USER AS 'system_user' GO REVERT GOThe first part was fine with the result of:WEB | WEB | WEB | WEB But the second result was a bit confusing:dbo | WEB | WEB | WEB What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one fail? Besides, of course, the first one being database level and the second one being server level impersonation, which I'm aware of and does not explain the situation here.
Being a member of sysadmin group is always mapped to dbo, even if it has an explicit user in the database. So it looks like the login WEB is a member of sysadmin, hence USER_NAME() correctly returns dbo in the second case. This will help you solve SQL server execution problems.