Unable to delete the database user as the user is already logged in currently. error 15434

Question

In SQL Server 2012, I have created a user called 'sagar' and mapped it to the XYZ database. Later I have deleted the XYZ database and now I don't need the existing user 'sagar' also. However when I attempt to delete the user from the SQL Server Management Studio. I am getting the below exception.

Could not drop login 'sagar' as the user is currently logged in. error:15434

Answer 1

The 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 = 'sagar'

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

Then you can delete the login just by running the below query or by using the SQL Server Management Studio options.

DROP LOGIN 'sagar'

 Want to learn more concepts related to SQL? Join this SQL Course by Intellipaat.