How Do I Kill All Sessions from a Windows Group?

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.

This entry was posted in Security, User Management, Utilities and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *