Hey there, the other day I got a call from a DBA saying he is not able to drop a login from a SQL Instance and getting the following error:
“Error 15434: Could not drop login as the user is currently logged in.”
In this blog post, we will discuss what this error message means, why it occurs and how to resolve it. First of all let’s see how the error message looks like SSMS.
This error message says database administrator tries to drop a login who is doing some work/running transactions in the SQL Server. First thought came to the DBA’s mind is to wait until the login/user disconnects to the database before they can drop it.
Why does Error 15434 occur in SQL Server?
The error occurs because you are trying to delete/drop a login which has a session (Active/Inactive) in the SQL Instance. The SQL server does not allow to drop the login because it would terminate the session. This can cause business impact.
Resolution:
To resolve Error 15434, the database administrator must first identify the connected session and then either wait for the session to be disconnected or manually disconnect the session. Always check with the user if possible. If it is more or like a generic ID like the above example (‘Admin’), it becomes little tricky.
When I got the call, first thing I checked with the DBA if he is trying to delete his own id. This situation may also occur when you Installed SQL Server and added your individual id and as part of the cleaning process you want to delete your id.
Point often overlooked is that you can not delete your own id. In that case you need to connect either using “sa” account or another “security admin” and “processadmin” (to be able to kill the process) or sysadmin account. If you don’t have another such account then ask your fellow DBA(s) to follow the below steps. Obviously SQL Server does not allow to kill your own session.
1. Identify the connected sessions:
USE master GO SELECT spid, blocked AS BlockedBy, loginame AS LogInName, login_time, last_batch, status FROM sys.sysprocesses WHERE loginame = 'Admin' --Change the loginID you are trying to delete
Sample Output:
Now few points to observe from the sample output. You got few sessions which for the login and they are in sleeping status. You need to to also observe the login_time and the last_batch which is basically the last activity time completed with the connection. If both of them are old, you can decide on killing the sessions. Again be very careful while you do this in production environment.
2. Disconnect or Kill the Sessions:
KILL spid
Screen Shot with above example Spids:
3. Delete the Login:
DROP Login [loginame]
As you can see from the above screen shot the login is dropped without any issue/errors.
Summary:
To conclude, the error “Error 15434: Could not drop login as the user is currently logged in” occurs when you try to delete a login having active/inactive connections to the SQL Instance. The database administrator must first identify and disconnect the connected session, and then drop the login.
You can also refer the below articles related to login drop issues:
Error 15141:The Server Principal Owns One or More Endpoints and cannot be Dropped
Error 15141: The Server Principal Owns One or More Availability Groups
