Execute as LOGIN works, execute as USER fails

Question

Execute as LOGIN works, execute as USER fails

webrunner

SSC-Dedicated

Points: 31866
More actions
July 16, 2019 at 3:42 pm

#3663497
Hello experts,
I'm puzzled by this behavior. Does someone know why the results are different and how (or whether) I need to modify the db user to make the EXECUTE AS USER work?
1. I granted db reader access to the login MYAD\mylogin to database MyDB, to test out permissions I gave the login to tables underlying a view.
2. When I use EXECUTE AS LOGIN='MYAD\mylogin' the results come back in SSMS.
3. When I use EXECUTE AS USER='MYAD\mylogin' I get this error:
Msg 916, Level 14, State 1, Line 6
The server principal is not able to access the database under the current security context.
Also note that in the login properties, MYAD\mylogin has Default Schema of dbo. But even when I changed the default schema to MYAD\mylogin I still got the error when using EXECUTE AS USER='MYAD\mylogin'.
I figure this issue is due to my ignorance on the security aspects in this situation, but I'm hoping someone can explain what they are and how I can use them correctly.
Thanks for any help.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

It can different things depending on whatever it is you are executing and from what database. Y0u'd also want to check what the user mapping is to that database - is the user mapped without the domain\ prefix. If this is something executed across databases it could be related to database owners. You can also hit that if the user is actually an orphan user in a restored database.

Sue

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

There could be many reasons - if the commands being executed access any data outside this database the command will fail. Can you confirm that the view(s) you are using are not accessing data in other databases?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 3

Please repro this with a new db and provide the code. The EXECUTE AS USER maps to a user. What user is mapped in your database.