Eliminar usuarios huérfanos SQL server
Eliminar usuarios huérfanos en SQL Server, puedes seguir estos pasos detallados y hacer uso de un script SQL que te ayudará a identificar y eliminar dichos usuarios de una manera ordenada. Aquí te dejo una guía detallada, que también podría ser útil como entrada de blog:
¿Por qué borrar usuarios huérfanos en SQL después de una migración?
Las migraciones de bases de datos son procesos complejos que, a menudo, pueden generar datos residuales no deseados, como usuarios huérfanos. Estos usuarios son cuentas que ya no están asociadas a ningún inicio de sesión válido y pueden suponer diversos riesgos para la seguridad y el rendimiento de la base de datos.
En este artículo, exploraremos las razones principales por las que es crucial eliminar los usuarios huérfanos en SQL después de una migración:
1. Mejorar la seguridad:
Los usuarios huérfanos representan una brecha de seguridad potencial, ya que podrían ser utilizados por actores maliciosos para acceder a la base de datos sin autorización. Al eliminarlos, se elimina este punto de entrada vulnerable y se protege la integridad de los datos.
2. Optimizar el rendimiento:
Las bases de datos con un gran número de usuarios huérfanos pueden experimentar un rendimiento más lento, especialmente durante las consultas que involucran permisos y roles. Eliminar estos usuarios innecesarios libera recursos del sistema y mejora la eficiencia general de la base de datos.
3. Simplificar la administración:
La presencia de usuarios huérfanos dificulta la administración de la base de datos, ya que aumenta la complejidad de tareas como la concesión de permisos y la auditoría de acceso. Eliminar estos usuarios simplifica el entorno y facilita la gestión de la base de datos.
4. Mejorar la calidad de los datos:
Los usuarios huérfanos pueden generar entradas de registro y datos de auditoría incorrectos, lo que dificulta la identificación de patrones y la comprensión del uso real de la base de datos. Eliminar estos usuarios garantiza que los datos de la base de datos sean precisos y confiables.
5. Cumplimiento normativo:
En algunos sectores, las regulaciones de cumplimiento de datos, como GDPR y HIPAA, exigen la eliminación de datos personales no utilizados. Eliminar usuarios huérfanos ayuda a garantizar el cumplimiento de estas regulaciones y protege la privacidad de la información sensible.
Script para borrar usuarios huérfanos y sus esquemas
---------------------------------------------------------------------------------------
-- ELIMINAR usuarios huérfanos y sus eschemas
---------------------------------------------------------------------------------------
-- www.sql-ninja.com
set nocount on
declare @cadena varchar(4000)
declare @usuario sysname
declare @nombre_usuario sysname
declare @principal_id int
declare @usuario2 sysname
---- coge los usuarios huérfanos (que no tiene un login con mismo nombre ni mismo codigo (SID)
select name, principal_id
into #usuarios --- tabla temporal con los usuarios
from sys.database_principals
where type in ('S','U','G')
and (sid is not null and sid <> 0x0)
and name not in (select name from sys.server_principals) -- no existe con mismo nombre
and sid not in (select sid from sys.server_principals) -- no existe con mismo sid
and principal_id > 1
and db_id() > 4
order by name
declare cur_usuarios cursor
for select name,principal_id from #usuarios
order by name
Open cur_usuarios
FETCH NEXT FROM cur_usuarios
INTO @nombre_usuario, @principal_id
WHILE @@FETCH_STATUS = 0
BEGIN
select @usuario= @nombre_usuario
select @usuario2='[' + @nombre_usuario +']'
declare @schema_name sysname , @cant_schemas int
set @schema_name = null
set @cant_schemas = 0
--- verifica la cantidad de schemas que tiene
select @cant_schemas = count(*) from sys.schemas where principal_id = @principal_id
print '********* USUARIO ---> ' + @nombre_usuario
if @cant_schemas > 0
begin
While @cant_schemas > 0
begin
select top(@cant_schemas) @schema_name = '[' + name +']' from sys.schemas where principal_id = @principal_id
print ' --- Se procedera a borrar el SCHEMA: ' + @schema_name
set @cadena=' drop schema ' + @schema_name + ''
print @cadena
exec ( @cadena ) ----- ELIMINA schema
set @cant_schemas = @cant_schemas - 1
end
end
---- select schema_name(s.schema_id),user_name(s.principal_id),*
print ' --- Se procedera a borrar el USUARIO: ' + @usuario
set @cadena=' drop user ' + @usuario2 + ''
print @cadena
exec ( @cadena ) ----- ELIMINA usuario
FETCH NEXT FROM cur_usuarios
INTO @nombre_usuario,@principal_id
END
CLOSE cur_usuarios
DEALLOCATE cur_usuarios
set nocount off
drop table #usuarios
goExplicación del código para borrar usuarios huerfános en SQL
Este script de SQL está diseñado para eliminar usuarios huérfanos y sus esquemas asociados en una base de datos. Los usuarios huérfanos son aquellos que no tienen un login correspondiente con el mismo nombre o el mismo SID (Security Identifier). Aquí está el desglose del script:
Parte Superior: Comentarios y Configuración Inicial
sqlCopiar código---------------------------------------------------------------------------------------
-- ELIMINAR usuarios huérfanos y sus esquemas --- Usuario que NO EXISTE LOGIN CON MISMO NOMBRE NI CON MISMO SID
---------------------------------------------------------------------------------------
-- www.sql-ninja.com
set nocount on
- Comentarios: Explica el propósito del script.
set nocount on: Previene que SQL Server devuelva mensajes de cuenta de filas afectadas, lo que hace que el script sea más limpio en términos de salida.
Declaración de Variables
declare @cadena varchar(4000)
declare @usuario sysname
declare @nombre_usuario sysname
declare @principal_id int
declare @usuario2 sysname
- Declaración de variables: Estas variables se utilizarán para almacenar información temporal durante la ejecución del script.
Selección de Usuarios Huérfanos
select name, principal_id
into #usuarios --- tabla temporal con los usuarios
from sys.database_principals
where type in ('S','U','G')
and (sid is not null and sid <> 0x0)
and name not in (select name from sys.server_principals) -- no existe con mismo nombre
and sid not in (select sid from sys.server_principals) -- no existe con mismo sid
and principal_id > 1
and db_id() > 4
order by name
select: Selecciona los usuarios que no tienen un login correspondiente con el mismo nombre o SID y los almacena en una tabla temporal#usuarios.
Cursor para Iterar sobre los Usuarios Huérfanos
declare cur_usuarios cursor
for select name,principal_id from #usuarios
order by name
Open cur_usuarios
FETCH NEXT FROM cur_usuarios
INTO @nombre_usuario, @principal_id
- Cursor: Define un cursor
cur_usuariospara iterar sobre los usuarios huérfanos seleccionados anteriormente.
Bucle para Procesar Cada Usuario Huérfano
WHILE @@FETCH_STATUS = 0
BEGIN
select @usuario= @nombre_usuario
select @usuario2='[' + @nombre_usuario +']'
declare @schema_name sysname , @cant_schemas int
set @schema_name = null
set @cant_schemas = 0
--- verifica la cantidad de schemas que tiene
select @cant_schemas = count(*) from sys.schemas where principal_id = @principal_id
print '********* USUARIO ---> ' + @nombre_usuario
if @cant_schemas > 0
begin
While @cant_schemas > 0
begin
select top(@cant_schemas) @schema_name = '[' + name +']' from sys.schemas where principal_id = @principal_id
print ' --- Se procedera a borrar el SCHEMA: ' + @schema_name
set @cadena=' drop schema ' + @schema_name + ''
print @cadena
exec ( @cadena ) ----- ELIMINA schema
set @cant_schemas = @cant_schemas - 1
end
end
---- select schema_name(s.schema_id),user_name(s.principal_id),*
print ' --- Se procedera a borrar el USUARIO: ' + @usuario
set @cadena=' drop user ' + @usuario2 + ''
print @cadena
exec ( @cadena ) ----- ELIMINA usuario
FETCH NEXT FROM cur_usuarios
INTO @nombre_usuario,@principal_id
END
- Bucle
WHILE: Itera sobre cada usuario huérfano.- Variables y Preparación: Asigna el nombre del usuario a las variables
@usuarioy@usuario2(con corchetes para manejar nombres con espacios o caracteres especiales). - Verificación de Esquemas: Cuenta la cantidad de esquemas asociados con el usuario.
- Eliminación de Esquemas: Si hay esquemas asociados, los elimina uno por uno.
- Eliminación de Usuario: Después de eliminar los esquemas, elimina el usuario.
- Variables y Preparación: Asigna el nombre del usuario a las variables
Cierre y Liberación de Recursos
CLOSE cur_usuarios
DEALLOCATE cur_usuarios
set nocount off
drop table #usuarios
go
- Cierre del Cursor: Cierra y libera el cursor.
- Restauración de
nocount: Vuelve a permitir que SQL Server devuelva mensajes de cuenta de filas. - Eliminación de Tabla Temporal: Elimina la tabla temporal
#usuarios.
Este script es útil para mantener la base de datos limpia al eliminar usuarios huérfanos y sus esquemas asociados, evitando así posibles problemas de seguridad y administración.
La importancia eliminar usuarios huérfanos en SQL después de una migración.
Más allá de las razones generales mencionadas en la entrada anterior, eliminar usuarios huérfanos en SQL después de una migración reviste especial importancia por las siguientes razones:
1. Complejidad de la migración:
Las migraciones de bases de datos, por su naturaleza compleja, pueden generar un mayor número de usuarios huérfanos debido a diversos factores, como:
- Eliminación incompleta de usuarios: Si la migración no elimina correctamente las cuentas de usuario asociadas a objetos de la base de datos origen, pueden quedar usuarios huérfanos en el destino.
- Cambios en la estructura de la base de datos: Si la estructura de la base de datos destino difiere de la origen, es posible que algunos usuarios ya no tengan permisos para acceder a los objetos relevantes, quedando huérfanos.
- Migración de datos incompleta: Si la migración no transfiere completamente los datos de permisos y roles, puede haber usuarios huérfanos en el destino que no estén asociados a ningún objeto o rol.
2. Mayor impacto en el rendimiento:
En el contexto de una migración, el impacto en el rendimiento causado por usuarios huérfanos puede ser más significativo, ya que:
- La base de datos destino es un nuevo entorno: Es posible que la base de datos destino no esté optimizada para manejar un gran número de usuarios huérfanos, lo que puede amplificar la degradación del rendimiento.
- Los recursos del sistema se encuentran en fase de adaptación: Tras la migración, la base de datos destino se encuentra en un proceso de adaptación a la nueva carga de trabajo. La presencia de usuarios huérfanos puede sobrecargar aún más el sistema durante esta fase crucial.
3. Riesgos de seguridad amplificados:
En el contexto posterior a una migración, los riesgos de seguridad asociados a usuarios huérfanos se intensifican:
- Entorno menos familiar: Los administradores del sistema pueden estar menos familiarizados con la nueva base de datos destino, lo que aumenta la probabilidad de pasar por alto usuarios huérfanos durante las revisiones de seguridad.
- Mayor superficie de ataque: Una migración puede ampliar la superficie de ataque de la base de datos, ya que los actores maliciosos pueden explorar los nuevos usuarios huérfanos como puntos de entrada vulnerables.
4. Mayor dificultad para la resolución de problemas:
La presencia de usuarios huérfanos puede dificultar la resolución de problemas después de una migración:
- Diagnóstico confuso: Los errores o fallos de funcionamiento pueden tener su origen en usuarios huérfanos, lo que dificulta la identificación precisa de la causa raíz del problema.
- Soluciones más complejas: La resolución de problemas relacionados con usuarios huérfanos puede requerir soluciones más complejas y que consumen más tiempo, desviando recursos de otras tareas importantes.
Cambiar el collation en un servidor sql server 2019
Creación de Roles en SQL Server
