[SQL] : Suppression de tous les utilisateurs d'une base SQL Server

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;