Блог

Главная - Вопросы по программированию - Как удалить пользователя из всех ролей базы данных в T-SQL

Как удалить пользователя из всех ролей базы данных в T-SQL

#sql-server

#sql-сервер

Вопрос:

Следующий запрос выбирает все роли базы данных, участником которых является @myUser:

 SELECT
    p.name AS userRoleName
FROM sys.database_role_members rm
INNER JOIN sys.database_principals p ON  p.principal_id = rm.role_principal_id
INNER JOIN sys.database_principals m ON  m.principal_id = rm.member_principal_id
WHERE m.name = @myUser;

Для каждой роли базы данных, перечисленных выше, я должен выполнить:

 ALTER ROLE [database_role_name] DROP MEMBER @myUser;

Как объединить два вышеуказанных запроса в одной процедуре T-SQL?

Комментарии:

1. Вам нужно будет скомпилировать динамический запрос, если вы используете последнюю версию SQL Server STRING_AGG , а затем выполнить его.

Ответ №1:

Мы можем сделать это с помощью динамического SQL. Я предполагаю, что у вас есть STRING_AGG доступная версия SQL Server:

 DECLARE @sql nvarchar(max) =
(SELECT
    STRING_AGG(
        CAST(N'ALTER ROLE '   QUOTENAME(p.name)   N' DROP MEMBER '   QUOTENAME(@myUser)   N';'
        AS nvarchar(max))
    , NCHAR(10))
FROM sys.database_role_members rm
INNER JOIN sys.database_principals p ON  p.principal_id = rm.role_principal_id
INNER JOIN sys.database_principals m ON  m.principal_id = rm.member_principal_id
WHERE m.name = @myUser
);

-- PRINT @sql  -- for testing
EXEC (@sql)
Метки: