Вам когда-нибудь приходилось выгонять всех пользователей из базы данных SQL Server перед выполнением критической операции? Возможно, вы хотите предотвратить вмешательство случайных пользователей в ваши еженочные сборки в хранилище данных. В этой статье мы рассмотрим хранимую процедуру, которая может помочь вам в этом.

Проблема

При выполнении определенных операций с базой данных SQL Server может потребоваться убедиться, что в данный момент нет подключенных пользователей. Это особенно важно при выполнении автоматизированных процессов или обслуживающих задач. Однако вручную отключать каждого пользователя может быть трудоемким и подверженным ошибкам.

Решение

Рассматриваемая нами хранимая процедура позволяет уничтожить все сеансы для пользователей, являющихся членами определенной группы домена. Отклоняя подключение к группе Windows, а затем вызывая эту хранимую процедуру, вы можете эффективно выгнать всех пользователей из базы данных. После завершения операции вы можете предоставить подключение группе, чтобы разрешить пользователям вход.

Код

Давайте посмотрим на код хранимой процедуры:

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_name LIKE '%\%'

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

Как это работает

Хранимая процедура извлекает всех пользователей, вошедших через аутентификацию Windows, ища косую черту в их имени входа. Затем она перебирает список пользователей, получая все группы Windows для каждого пользователя. Если какая-либо группа Windows, к которой принадлежит пользователь, соответствует переданной в качестве параметра, сеанс уничтожается с помощью команды KILL.

Заключение

Эта хранимая процедура может быть полезным инструментом для управления сеансами SQL Server и обеспечения отсутствия подключенных пользователей при выполнении критических операций. Однако важно быть осторожным и проверять членство в группе перед уничтожением сеансов. Также рекомендуется проверять и понимать любые скрипты, полученные из Интернета, перед их использованием.

Мы надеемся, что эта хранимая процедура будет полезной в вашей работе с администрированием SQL Server. Если у вас возникнут проблемы или предложения по улучшению, пожалуйста, дайте нам знать. Удачного кодирования!