Kürzlich I experienced another surprise with EXECUTE AS LOGIN. It is quite possible to carry out the impersonation even for a disabled or even deleted login. Here's an example:
I put on Windows a user "test", for which I then create a login in SQL Server. Then I set for this login to a user on the Northwind database and SELECT permissions to give him. Here's the script this:
use [master]
go
create login [deep thought \\ test] from windows
with default_database = [master]
go
use [northwind]
go
createuser [deep thought \\ test] for login [Deep thought \\ test]
go
use [northwind]
go
exec sp_addrolemember N'db_datareader 'N'DeepThought \\ test'
go
The database user can [deep thought \\ test] now on the Northwind database as data query. By EXECUTE AS LOGIN untetr another, this can also log in to be checked, like so:
execute as login = 'deep thought \\ test' select * from Customers
revert
The SELECT statement works without problems.
Now, if the login is disabled and also the login is denied permission to log on, then one would think so, that the above SELECT in the context of EXECUTE AS LOGIN not working anymore. This is not so! If I login with this script Override:
use [master]
go
deny connect sql to [deep thought \\ test]
go
old login [deep thought \\ test] disable
go
then does the SELECT statement in the context of the disabled logins as before, although an application in the security context is not at all possible! Now, one might even argue that it's not about to check whether the test is allowed to login at all. EXECUTE AS LOGIN tests easy, which would be allowed in the security context of the application if the application for successful would run. I was there but a bit confused, because I use EXECUTE AS often to test this behavior and I find it quite strange.
