March 20, 2021 at 8:49 pm
Hi!
Let's assume I'm doing "execute as login='user1' " and that user 1 only has access to northwind_spp database. If I try to run something under it such as "select * from adventureworks2019" it will give an error that this user1 does not have access to that database. However, if I go to the left side under object explorer and manually click on all the other databases/tables/objects that user1 should NOT have access to, it lets me expand and even click to see the top 1000 rows. Does this mean that the impersonation is only limited to whatever is written out in the query window? I'm assuming GUI is still working with my admin credentials, and in order to see what user1 has access to, I either have to log into SQL with their credentials, or limit my query to script typed into the window? Sorry I'm not even sure what the window/tab/query box is technically called. thanks a bunch!
March 21, 2021 at 10:22 am
Hi sqlnoob,
Yes, you're correct that the impersonation is limited to the current query tab. My understanding is that each query tab has it's own session, and EXECUTE AS is scoped to the session context. The object explorer runs under a different session to your queries, so EXECUTE AS won't impact this.
More information on EXECUTE AS being scoped to the current session can be found in the docs here. Both of your suggested workarounds are good - either running SSMS as the relevant Windows user so you can see what they see, or keeping your analysis to the single query tab.
Best,
Andrew
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply