¿Alguna vez has necesitado eliminar un usuario de todas las bases de datos de tu instancia de SQL Server? Hacerlo manualmente puede ser tedioso y propenso a errores, sobre todo si tienes muchas bases de datos. Por eso, hoy te comparto un script automatizado y seguro que te ayudará a realizar esta tarea de forma eficiente y controlada.
¿Por qué eliminar usuarios de todas las bases de datos?
Eliminar usuarios antiguos o innecesarios es una buena práctica de seguridad. Así evitas accesos no autorizados y mantienes tu entorno limpio y ordenado. Además, puede ser necesario cuando un colaborador deja la empresa, cuando migras aplicaciones o durante auditorías de seguridad.
Desafíos al eliminar usuarios en SQL Server
Eliminar un usuario de una base de datos no siempre es tan simple como ejecutar DROP USER. Pueden surgir problemas como:
- El usuario es propietario de esquemas u objetos.
- El usuario pertenece a roles de base de datos.
- El usuario tiene conexiones activas.
- El usuario no existe en todas las bases de datos.
Por eso, es fundamental usar un script que gestione estas dependencias y te informe de cualquier inconveniente.
Script completo para eliminar un usuario de todas las bases de datos
A continuación, te presento un script listo para usar. Solo debes cambiar el nombre del usuario que deseas eliminar, ejecutarlo en tu instancia de SQL Server y revisar los resultados.
USE master;
GO
DECLARE @UsuarioAEliminar NVARCHAR(128) = N'NOMBRE_USUARIO_AQUI'; -- Cambia esto por el nombre del usuario
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DBName NVARCHAR(128);
DECLARE @ErrorMsg NVARCHAR(MAX);
-- Tabla temporal para registrar los resultados
IF OBJECT_ID('tempdb..#Resultados') IS NOT NULL DROP TABLE #Resultados;
CREATE TABLE #Resultados (
BaseDeDatos NVARCHAR(128),
UsuarioExiste BIT,
AccionTomada NVARCHAR(MAX),
MensajeError NVARCHAR(MAX)
);
-- Cursor para recorrer todas las bases de datos (excepto las de sistema)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state = 0 -- Solo bases de datos en línea
AND is_read_only = 0 -- Excluye bases de datos de solo lectura
AND name NOT IN ('master', 'tempdb', 'model', 'msdb'); -- Excluye bases de datos del sistema
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ErrorMsg = NULL;
-- Script dinámico por cada base de datos
SET @SQL = N'
USE [' + @DBName + N'];
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @UsuarioAEliminar + N''')
BEGIN
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (''' + @DBName + N''', 1, ''Intentando eliminar usuario'', NULL);
BEGIN TRY
-- Transferir propiedad de esquemas a dbo
DECLARE @SchemaSQL NVARCHAR(MAX) = '''';
SELECT @SchemaSQL = @SchemaSQL + ''ALTER AUTHORIZATION ON SCHEMA::'' + QUOTENAME(name) + '' TO dbo; ''
FROM sys.schemas
WHERE principal_id = USER_ID(''' + @UsuarioAEliminar + N''');
IF LEN(@SchemaSQL) > 0
BEGIN
EXEC(@SchemaSQL);
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (''' + @DBName + N''', 1, ''Propiedad de esquemas transferida a dbo'', NULL);
END
-- Eliminar usuario de roles
DECLARE @RoleSQL NVARCHAR(MAX) = '''';
SELECT @RoleSQL = @RoleSQL + ''ALTER ROLE '' + QUOTENAME(r.name) + '' DROP MEMBER '' + QUOTENAME(''' + @UsuarioAEliminar + N''') + ''; ''
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = ''' + @UsuarioAEliminar + N''';
IF LEN(@RoleSQL) > 0
BEGIN
EXEC(@RoleSQL);
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (''' + @DBName + N''', 1, ''Usuario eliminado de roles de base de datos'', NULL);
END
-- Eliminar el usuario
DROP USER [' + @UsuarioAEliminar + N'];
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (''' + @DBName + N''', 1, ''Usuario eliminado correctamente'', NULL);
END TRY
BEGIN CATCH
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (''' + @DBName + N''', 1, ''Error al eliminar usuario'', ERROR_MESSAGE());
END CATCH
END
ELSE
BEGIN
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (''' + @DBName + N''', 0, ''El usuario no existe en esta base de datos'', NULL);
END';
BEGIN TRY
EXEC sp_executesql @SQL;
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE();
INSERT INTO #Resultados (BaseDeDatos, UsuarioExiste, AccionTomada, MensajeError)
VALUES (@DBName, NULL, 'Error accediendo a la base de datos', @ErrorMsg);
END CATCH
FETCH NEXT FROM db_cursor INTO @DBName;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
-- Mostrar los resultados
SELECT BaseDeDatos,
CASE WHEN UsuarioExiste = 1 THEN 'Sí' WHEN UsuarioExiste = 0 THEN 'No' ELSE 'Desconocido' END AS UsuarioExiste,
AccionTomada,
MensajeError
FROM #Resultados
ORDER BY BaseDeDatos;
DROP TABLE #Resultados;
¿Cómo usar este script?
- Reemplaza
NOMBRE_USUARIO_AQUIpor el nombre del usuario que deseas eliminar. - Ejecuta el script en tu instancia de SQL Server (con permisos de administrador).
- Revisa la tabla de resultados al final para verificar qué ocurrió en cada base de datos.
Consideraciones importantes
- Permisos necesarios: Debes tener permisos de administrador en la instancia y en cada base de datos.
- Respaldo: Realiza un respaldo antes de hacer cambios masivos.
- Eliminación de login: Este script elimina el usuario de las bases de datos, pero no el login del servidor. Si también quieres eliminar el login, puedes agregar al final:
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @UsuarioAEliminar) BEGIN DECLARE @LoginSQL NVARCHAR(MAX) = 'DROP LOGIN [' + @UsuarioAEliminar + '];'; EXEC sp_executesql @LoginSQL; PRINT 'Login eliminado: ' + @UsuarioAEliminar; END - Conexiones activas: Si el usuario tiene sesiones activas, puede que necesites cerrarlas antes de eliminarlo.
Conclusión
Con este script puedes mantener tu entorno SQL Server más seguro y ordenado, eliminando usuarios de forma masiva y controlada. Si te resultó útil, ¡compártelo con otros administradores o deja tus dudas en los comentarios!
¿Te gustaría más scripts o consejos sobre administración de SQL Server? ¡Déjamelo saber en los comentarios!
