Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
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.

share|improve this question
    
The 2nd one isn't "failing", it's just telling you that the login has a server role (sysadmin) assigned. Have you checked the schema's that are being requested by the user? Had that before where the dbo default schema isn't the one the devs had expected it to be. Better fixed in the code by fully referencing the table –  Stuart Moore Oct 2 '12 at 10:55

1 Answer 1

up vote 4 down vote accepted

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 sysadmin, hence USER_NAME() correctly returns dbo in the second case.

share|improve this answer
    
Checked and confirmed. It was a member of sysadmin indeed. It turned out that this report has reached one of the other developers and instead of the correct solution he simply assigned the login sysadmin role. I wouldn't even think about that happening, I'm truly disappointed. We're going to have some serious talk when he comes back. Learned something new today, thanks. –  gemisigo Oct 2 '12 at 11:09
    
It also means that your application (report?) has impersonate permission on a sysadmin member. Look into how exactly in the permission hierarchy is this granted. Is likely that even now the report can, if it wishes, impersonate another sysadmin member, so the reporting application is a de-facto sysadmin. Ie. i a possible elevation of privileges attack vector, it can be used by a hacker or, more likely, by your staff. –  Remus Rusanu Oct 2 '12 at 11:33
    
If the repor app has IMPERSONATE permission on WEB and WEB was removed from sysadmin then the door is closed. But is worth checking none the less. –  Remus Rusanu Oct 2 '12 at 11:34
    
Yes, we'll have to check everything affecting rights once again to see if there were other issues "fixed" this way. Not very happy right now :( Thanks again, Remus. –  gemisigo Oct 2 '12 at 11:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.