46

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?

EMP
  • 59,148
  • 53
  • 164
  • 220

6 Answers6

61

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
EMP
  • 59,148
  • 53
  • 164
  • 220
15

User can be deleted after killing the session by identifying the session_id of the user.

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'

KILL 69  --69 is session_id here, you may get different id

Now you can delete the login simply by executing the below query (or) by using sql server management studio options.

DROP LOGIN 'test'
adiga
  • 34,372
  • 9
  • 61
  • 83
Darren G
  • 193
  • 2
  • 9
9

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]

pasx
  • 2,718
  • 1
  • 34
  • 26
  • Cannot kill your own process – user1040323 Jul 18 '18 at 13:41
  • I believe the original question was about dropping a login, not your own. I haven't been using SQL server for a while now so I can't tell you what to do if you want to drop your own login. Maybe simply log out in all the windows you logged in. – pasx Jul 19 '18 at 15:42
  • If you need to filter the results of sp_who2, take a look at this post: https://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2 – pasx Apr 02 '19 at 09:19
4
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

as SqlACID said, or you could find all processes and kill one at time and then drop user

Kris Ivanov
  • 10,476
  • 1
  • 24
  • 35
  • BTW, if you need to restore the DB to its original status use the same command with the MULTI_USER option. Cf. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15 – pasx Jul 06 '21 at 17:23
3

This may not always be applicable but I've just restarted the SQL server from services. That cleared up any open connections.

Tiele Declercq
  • 2,070
  • 2
  • 28
  • 39
2

If you know what database they will be connected to, you could set the database to single user mode, with rollback immediate, that'll drop their sessions.

SqlACID
  • 4,024
  • 20
  • 28
  • Yes, I do this. In fact, I actually drop their database first, but sometimes (very rarely) this error still occurs. – EMP Feb 11 '11 at 04:32
  • I suppose you could get the spid of the account's connections from sys.sysprocesses and build a 'KILL '+ spid statement to execute. – SqlACID Feb 11 '11 at 17:50