I have a C# application that communicates with SQL Server.
When I try to drop a user that recently had a connection with SQL Server, it gives me an error that I can't delete them because they have connections open in the database.
All SQL queries are appropriately wrapped in using statements but when I run sp_who, it tells me that that person has a connection in a sleeping state, I have researched this and found out that this is normal behavior meant for speed improvements.
To get rid of this connection, I used SqlConnection.ClearPool which works and I can now delete the user but then the queries becomes a decent amount slower, which I expected and know the reasons why it's happening.
What's the best way to deal with this? Force the connections closed only when trying to delete the person somehow? If so, how is this done?
Thanks in advance!