Sample Header Ad - 728x90

Why does killing a SQL Connection allow a reconnect with SSMS

-2 votes
3 answers
268 views
I would like to kill connections for individuals using certain sql auth logins in SSMS that are reserved for our apps. They should have to go out of their way to reauthenticate into sql and at least feel a little pain for doing something they shouldn't be. This is the query similar to one I use to identify spids that should be killed. I would take those spids and iterate through and KILL them in the same fashion I have seen in many other posts. I believe my method actually uses a cursor, but that is neither here nor there. SELECT sp.spid FROM sys.sysprocesses sp JOIN sys.server_principals sl ON sl.sid = sp.sid WHERE sl.type='S' AND sp.Program_Name LIKE 'Microsoft SQL Server Management Studio%' and sp.loginame NOT IN ('pilon','validlogin1','validlogin2') I have tried to kill spids that fit this criteria and then all they have to do is run a query and it reconnects. I found this MS Documentation on the ALTER LOGIN command, that would lead me to believe that is the expected behavior, but I have found the opposite. Is there a different way to go about this than 'KILL 1234'? Is there an additional 'FORCE' command or something that I am not aware of? I have considered using the Resource Governor, but don't want to incur the overhead necessary to make that work. I have no idea if this is even possible. Any suggestions are welcome. We have considered a shame email as well, but that is a little more in your face than I am after.
Asked by Laura Pilon (1 rep)
Jun 28, 2024, 08:02 PM
Last activity: Jun 30, 2024, 02:19 PM