Thursday, 4 August 2011

Admin: can't drop impersonated login

This week, I came across an interesting behaviour while working with EXECUTE AS. I'd set up a test database to demo some work with a test login and when trying to tidy up the objects, I was faced with:

I can repro the behaviour with the following example. NB: there are 2 separate connections in this script.

/*
CONNECTION 1
*/

-- create a test database
CREATE DATABASE TestDropLogin;
GO
-- and a test login
CREATE LOGIN DropLogin
WITH PASSWORD = 'abc';
GO
USE TestDropLogin
GO
-- and finally a test user for that login
CREATE USER [DropLogin]
FOR LOGIN [DropLogin]
GO

EXECUTE AS LOGIN = 'DropLogin'
GO
SELECT ORIGINAL_LOGIN() -- 'DOMAIN\MyUser'
SELECT SUSER_SNAME() -- 'DropLogin'
GO
USE [master]
GO

/*
CONNECTION 2
*/
-- drop the database as we no logner need it
DROP DATABASE TestDropLogin;
GO
-- try and drop the login too
DROP LOGIN [DropLogin]
GO

Msg 15434, Level 16, State 1, Line 1
Could not drop login 'DropLogin' as the user is currently logged in.

That's because I didn't revert my execution context earlier and so I'm still logged in as DropLogin. If I go back to the 1st connection I can run the REVERT command.

Msg 15199, Level 16, State 1, Line 1
The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

But i've dropped the database where EXECUTE AS was called so issuing a USE command gives us:

Msg 911, Level 16, State 1, Line 1
Database 'TestDropLogin' does not exist. Make sure that the name is entered correctly.

So I don't appear to be able to REVERT the execution context and therefore can't drop the login. The only solution I found to this was to force the connection to close by issuing a KILL command.

No comments:

Post a comment

/* add this crazy stuff in so i can use syntax highlighter