About this question
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.
