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
-- 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 GO
The 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, of what I’m aware of and does not explain the situation here.
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 member of
USER_NAME() correctly returns
dbo in the second case.