Introduction
The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse. I have created two jobs for each group of users that I want to keep out of the way. The first job denies connect to the Windows Group then calls this stored procedure to kick all of the users off. I run this job just before the build begins. The second job grants connect to the group after the nightly build finishes.
The Script
The stored procedure gets all users logged in via Windows Authentication by looking for the slash in their login name, cursoring through the list of users getting all windows groups for each user. If any windows groups that user is a member of match the one passed in then the session is killed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | CREATE PROCEDURE dbo.sp_dba_kill_spids_for_domain_group(@domain_group sysname) AS SET NOCOUNT ON DECLARE @spid smallint, @loginame nvarchar(128), @command nvarchar(max) CREATE TABLE #Logins account_name sysname NULL, type char(8) NULL, privilege char(9) NULL, mapped_login_name sysname NULL, permission_path sysname NULL DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR SELECT RTRIM(login_name), session_id FROM sys.dm_exec_sessions WHERE login_nameLIKE '%\%' --Look for the slash between domain and login OPEN csr FETCH NEXT FROM csr INTO @loginame, @spid WHILE @@FETCH_STATUS <> -1 BEGIN INSERT #Logins EXEC xp_logininfo @loginame, 'all'
IF EXISTS( SELECT * FROM #Logins WHERE permission_path = @domain_group ) BEGIN SELECT @command = 'KILL ' + CAST(@spid AS nvarchar) + ' --' + @loginame PRINT @command EXEC sp_executesql @command END TRUNCATE TABLE #Logins FETCH NEXT FROM csr INTO @loginame, @spid END CLOSE csr DEALLOCATE csr DROP TABLE #Logins SET NOCOUNT OFF GO |
Conclusion
As usual, I hope you find this stored procedure helpful. Be careful that you know who is in a group before killing the sessions. I have seen situations where people found out they were in the wrong group because their sessions kept getting killed. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
