Dans cet article, je vais parler d'un sujet que je viens de traiter et qui consiste à faire le ménage au niveau des bases de données récupérées d'un serveur et restaurées sur un autre. Et cela en supprimant tous les utilisateurs de ces bases de données, afin d'y appliquer une sécurité un peu plus restrictive.
Avant de pouvoir supprimer des utilisateurs ou des rôles SQL, il faut allouer tous les schémas au rôle "dbo". En effet, on ne peut pas supprimer un utilisateur ou un rôle qui est "owner" d'un schéma.
Pour cela, un curseur sur tous les schémas :
DECLARE
@SchemaName SYSNAME,@SQL
NVARCHAR(1000)
DECLARE SchemaNames
CURSOR FOR
SELECT s.name FROM sys.schemas s
WHERE s.principal_id NOT IN (SELECT dp.principal_id
FROM sys.database_principals
dp
WHERE dp.is_fixed_role=1 OR name IN ('dbo','guest','INFORMATION_SCHEMA','sys','public'))
OPEN
SchemaNames
FETCH NEXT FROM SchemaNames
INTO @SchemaName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER AUTHORIZATION ON SCHEMA::[' + @SchemaName + '] TO dbo'
EXEC sp_executesql @SQL
FETCH NEXT FROM SchemaNames INTO
@SchemaName
END
CLOSE
SchemaNames
DEALLOCATE SchemaNames;
La deuxième étape consiste à supprimer les utilisateurs ainsi que les rôles de base de données, et là aussi il y a une contrainte, c'est qu'on ne peut pas supprimer un utilisateur référencé dans un rôle.
Pour cela, la suppression sera faite en deux étapes:
- Suppression des utilisateurs référencés dans chaque rôle via un "sp_droprolemember" avant de supprimer le rôle même.
- Puis suppression des utilisateurs.
Voici le script complet :
DECLARE
@LoginRoleName SYSNAME,@SQL
NVARCHAR(1000),@Type VARCHAR(1)
DECLARE
DBROLELOGINS CURSOR FOR
SELECT NAME,[type] FROM sys.database_principals
WHERE name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys','public')
AND
is_fixed_role = 0
OPEN
DBROLELOGINS
FETCH NEXT FROM
DBROLELOGINS INTO @LoginRoleName, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Type = 'R'
BEGIN
--DECLARE @RoleName sysname
--set @RoleName = @LoginRoleName
BEGIN
DECLARE @RoleMemberName sysname
DECLARE Member_Cursor CURSOR
FOR
select [name]
from sys.database_principals
where principal_id in (
select member_principal_id
from sys.database_role_members
where role_principal_id in (
select principal_id
FROM sys.database_principals
where [name] =
@LoginRoleName AND
type = 'R' ))
OPEN Member_Cursor;
FETCH NEXT FROM Member_Cursor
into @RoleMemberName
WHILE @@FETCH_STATUS
= 0
BEGIN
exec sp_droprolemember @rolename=@LoginRoleName, @membername=
@RoleMemberName
PRINT @LoginRoleName
FETCH NEXT FROM Member_Cursor
into @RoleMemberName
END;
CLOSE Member_Cursor;
DEALLOCATE Member_Cursor;
END
SET @SQL = 'DROP ROLE [' + @LoginRoleName + ']'
EXEC sp_executesql @SQL
PRINT @SQL
END
IF @Type <> 'R'
BEGIN
SET @SQL = 'DROP USER [' + @LoginRoleName + ']'
PRINT @SQL
EXEC sp_executesql @SQL
END
FETCH NEXT FROM
DBROLELOGINS INTO @LoginRoleName,@Type
END
CLOSE
DBROLELOGINS
DEALLOCATE
DBROLELOGINS;
