Drop SQL login even while logged in

Question

When dropping a SQL Server 2008 login as part of integration test execution I sometimes get the following error:

System.Data.SqlClient.SqlException: Could not drop login 'SomeTestUser' as the user is currently logged in.

I don't care if it's logged in - I still want to drop it. What's the easiest way to do this?

Answer 1

OK, here's the script I came up with, which worked for me. Note that you need to be a member of the processadmin server role to find and kill the connection and a member of securityadmin to drop the login. (Of course, sysadmin can do anything.)

DECLARE @loginNameToDrop sysname SET @loginNameToDrop = '<victim login ID>';  DECLARE sessionsToKill CURSOR FAST_FORWARD FOR     SELECT session_id     FROM sys.dm_exec_sessions     WHERE login_name = @loginNameToDrop OPEN sessionsToKill  DECLARE @sessionId INT DECLARE @statement NVARCHAR(200)  FETCH NEXT FROM sessionsToKill INTO @sessionId  WHILE @@FETCH_STATUS = 0 BEGIN     PRINT 'Killing session ' + CAST(@sessionId AS NVARCHAR(20)) + ' for login ' + @loginNameToDrop      SET @statement = 'KILL ' + CAST(@sessionId AS NVARCHAR(20))     EXEC sp_executesql @statement      FETCH NEXT FROM sessionsToKill INTO @sessionId END  CLOSE sessionsToKill DEALLOCATE sessionsToKill  PRINT 'Dropping login ' + @loginNameToDrop SET @statement = 'DROP LOGIN [' + @loginNameToDrop + ']' EXEC sp_executesql @statement 

Answer 2

In SqlServer Studio on the Master DB.

Use the command sp_who2 to list the opened sessions.

In the list find the spid for your user - there may be more than one - e.g. 999

Use kill and the spid to close all the sessions e.g.: kill 999

Then DROP LOGIN [theuser]