Fix SQL Server Error 15434: Could not drop login ‘Domain\abc’ as the user is currently logged in

Generally, Microsoft SQL Server error 15434 appears during removing a SQL Server login. This error generates when you try to drop a login that has an active open connection to SQL Server Instance. Here i will discuss all the aspects of this error and the its resolution.

If you are facing different kind of DROP login failed errors, you can read below articles where i have demonstrated step by step method to fix the various kind of DROP login failed errors:

• How to fix Error 15174: Login owns one or more databases.
• How to fix Error 15173: Revoke the permissions before dropping the login.
• How to fix error 15138 & Error 3729: DROP login failed
• How to fix Error 15141: Server principal owns one or more endpoints.

Microsoft SQL Server Error 15434

This error generates when a login has made a connection to the SQL Server Instance and you are trying to drop the same login. Solution to fix this issue is to close all sessions which are opened by this login and then drop it. You can get the details about the sessions which are open using this login id either by sp_who2 or by DMV sys.dm_exec_sessions.

I had to remove a domain login on one of SQL Server instance but login was failed to drop because of SQL Server error 15434 which suggests that the login is currently logged in. Error details are given below.

Drop failed for Login “domain\abc”
Could not drop login ‘domain\abc’ as the user is currently logged in. (Microsoft SQL Server, Error: 15434)

As error clearly says that above login is currently logged in. Run below T-SQL code to get the details about the sessions or connections this login has made to the SQL Server Instance.

SELECT login_name, * FROM sys.dm_exec_sessions
WHERE login_name = 'xyz\abc'
Go

You will see the details for this login along with the session id in below screenshot.

Solution

Now we have identified the session id which are running using this login id which we want to drop. Now next step is to kill this session. Make sure to check the SQL code running behind this session id. If it is not critical go ahead and drop the login or if any DML or other operations are running about which you are not aware of, please contact application owners to get the details about this session id. Don’t kill any session id that you are not aware of. Now go ahead and kill above session which is running using our login id. I am going ahead to kill this session because i know what is running under this session which is not critical.

--Replace the session id 53 with your session id.
Kill 53
GO

We can see command executed successfully. You can recheck whether this session is still open or not by executing same command which we have executed in first section.

We can see, session id 53 is no more active and disappeared from the SQL Server transactions. Next step is go ahead and drop the login again. This time your login will be dropped successfully.

If you are still facing same issue during dropping the login and get this error again then you can check SQL Server services log on accounts. Sometimes if you run SQL Server services using the account which you want to drop throws same error. If this is the case then your next step is to change the logon account to some other domain account to release this login. You can see this in below image. Change the log on account of all SQL Server services if they are running using this account. Once you made the changes to the services, restart SQL Server service to bring new account in use by SQL Server.

Now go ahead and drop the account again. This time it will work and your login will be removed. You will not face Microsoft SQL Server Error 15434 anymore. You can do it by either SSMS or T-SQL. SSMS way is given below:

• Connect to target SQL Server Instance.
• Expand the security folder.
• Expand the Logins folder.
• Right click and choose delete on the identified login which needs to be deleted.
• Click on Ok button of the login deletion window.

If you want to drop this login using T-SQL command then open a query window and execute below command.

--change the name of Login_name with your login name which you want to delete.
DROP Login 'LOGIN_NAME'

If you are getting some another issue during dropping this login, you can read below articles where i have demonstrated step by step method to fix the DROP login failed errors:

• How to fix Error 15174: Login owns one or more databases.
• How to fix Error 15173: Revoke the permissions before dropping the login.
• How to fix error 15138 & Error 3729: DROP login failed
• How to fix Error 15141: Server principal owns one or more endpoints.

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.