Generando Script de creación de Usuarios en SQL Server
En el entorno de administración de bases de datos, es común encontrarnos con la necesidad de generar scripts automatizados para tareas repetitivas como la creación de usuarios. En SQL Server, este proceso se simplifica utilizando consultas SQL que extraen información de metadatos del sistema para construir declaraciones CREATE USER
personalizadas. A continuación, exploraremos cómo puedes generar estos scripts de manera eficiente.
Consulta SQL
La consulta SQL proporcionada tiene como objetivo generar comandos CREATE USER
para todos los usuarios relevantes en una base de datos específica. Vamos a desglosar cada parte de la consulta para entender cómo funciona:
sqlCopiar
SELECT
'CREATE USER ['+ dbU.[name] + '] FOR LOGIN [' + dbU.[name] + '] WITH DEFAULT_SCHEMA = [' +dbU.default_schema_name +'];'
FROM
sys.database_principals AS dbU
WHERE (TYPE='S' or TYPE='U' or TYPE='G') aND dbU.default_schema_name is not null
GO
Este script recorre los usuarios de la base de datos que tienen un esquema predeterminado asignado y crea usuarios de SQL Server correspondientes con inicios de sesión asociados. Si se proporciona un esquema predeterminado, también se asigna al usuario recién creado.
Desglose del script para sacar todos los usuarios de una base de datos
- Consulta de la base de datos:
sys.database_principals AS dbU
:sys.database_principals
es una vista del sistema que contiene información sobre los principios de seguridad en la base de datos actual (dbU
es un alias para esta tabla).WHERE (TYPE = 'S' OR TYPE = 'U' OR TYPE = 'G')
: Filtra los resultados para incluir solo usuarios ('S'
), usuarios de Windows ('U'
) y grupos ('G'
).AND dbU.default_schema_name IS NOT NULL
: Asegura que solo se seleccionen aquellos principios de base de datos que tienen un esquema predeterminado definido.
- Resultado de la consulta: La consulta devuelve una lista de comandos
CREATE USER
para cada usuario encontrado ensys.database_principals
que cumpla con los criterios especificados. El comandoCREATE USER
crea un usuario en la base de datos para un login específico, asignando el esquema predeterminado indicado pordbU.default_schema_name
. - Ejecución del script:
- Una vez obtenidos los comandos
CREATE USER
, generalmente se ejecutan en el contexto de la base de datos para crear los usuarios con los esquemas predeterminados correspondientes.
- Una vez obtenidos los comandos
En resumen, este script automatiza la creación de usuarios en una base de datos SQL Server para los logins relevantes, asegurándose de asignarles un esquema predeterminado válido si está definido en la base de datos.
Beneficios y Aplicaciones Prácticas
- Automatización: Esta consulta puede ser integrada en scripts más grandes de administración de base de datos para automatizar la creación de usuarios durante el despliegue de una aplicación o la configuración inicial de un entorno.
- Seguridad y Gestión: Al automatizar la creación de usuarios, se asegura la consistencia y la seguridad, evitando errores humanos y garantizando que todos los usuarios tengan configuraciones adecuadas.
- Escalabilidad: A medida que crece el número de usuarios y bases de datos, el uso de scripts SQL como este facilita la gestión y reduce la carga administrativa.
Conclución
El uso de consultas SQL como la presentada no solo simplifica tareas administrativas complejas, sino que también mejora la eficiencia operativa y reduce el riesgo de errores. Al integrar estas prácticas en tu flujo de trabajo de administración de bases de datos, puedes optimizar significativamente la gestión y la seguridad de tus entornos SQL Server.
Descarga de SQL Server Management Studio (SSMS)