Como administrador de bases de datos (DBA), hay momentos en los que necesitas exportar los permisos de usuario de la base de datos a un archivo. Esto puede ser por diversas razones, como recuperación ante desastres, auditoría, transferencia a otra base de datos o instancia, o crear un nuevo usuario con permisos similares. En esta publicación del blog, exploraremos cómo el comando Export-SqlUser del módulo PowerShell dbatools puede ayudar en estas situaciones.

El módulo dbatools es un módulo PowerShell creado por la comunidad para simplificar las tareas de administración de SQL Server. Consta de 182 comandos separados, y uno de ellos es Export-SqlUser, que fue desarrollado por Cláudio Silva, un Microsoft MVP. Para comenzar, puedes instalar el módulo dbatools siguiendo las instrucciones disponibles en su sitio web.

El comando Export-SqlUser exporta la creación de usuarios y sus permisos a un archivo T-SQL o al host. Incluye el usuario, crea y agrega a roles, permisos a nivel de base de datos, permisos a nivel de objeto y declaraciones Create Role para cualquier rol. Sin embargo, no genera declaraciones IF NOT EXISTS, lo cual podría ser una mejora. Además, excluye las bases de datos del sistema, por lo que si necesitas generar un script para usuarios que requieren acceso a esas bases de datos, debes tener en cuenta esta limitación. Cláudio es consciente de estas limitaciones y está trabajando en mejorar el código para eliminarlas.

El comando Export-SqlUser toma varios parámetros:

• SqlInstance: El nombre de la instancia de SQL Server. Admite SQL Server 2000 y versiones posteriores.
• User: Exporta solo el/los usuario(s) de la base de datos especificada. Si no se especifica, exportará todos los usuarios de la(s) base(s) de datos.
• DestinationVersion: Especifica la versión de SQL para la cual se debe generar el script. Si no se especifica, utilizará el nivel de compatibilidad de la base de datos actual.
• FilePath: La ruta de archivo donde se escribirá el T-SQL exportado.
• SqlCredential: Te permite iniciar sesión en servidores utilizando credenciales alternativas.
• NoClobber: No sobrescribe el archivo.
• Append: Agrega al archivo.
• Databases: Un parámetro dinámico que te permite especificar una o varias bases de datos.

Veamos el comando Export-SqlUser en acción:

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Usuarios.sql
notepad C:\temp\SQL2016N2-Usuarios.sql

Este comando exporta todos los usuarios de la instancia SQL2016N2 y guarda el script T-SQL en la ruta de archivo especificada. Luego, puedes abrir el archivo en Notepad o cualquier otro editor de texto para ver los permisos exportados.

Si deseas exportar usuarios de una base de datos específica, puedes usar el parámetro Databases:

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Fadetoblack.sql -Databases Fadetoblack
notepad C:\temp\SQL2016N2-Fadetoblack.sql

Este comando exporta solo los usuarios de la base de datos “Fadetoblack” y guarda el script T-SQL en la ruta de archivo especificada.

Exportar permisos de usuario utilizando el comando Export-SqlUser no solo es fácil sino también eficiente. En comparación, escribir el script T-SQL equivalente manualmente requeriría más esfuerzo y tiempo. Por ejemplo, un script encontrado en SQLServerCentral consta de 262 líneas y requeriría pasos adicionales para guardarlo en un archivo.

Además, el comando Export-SqlUser te permite modificar fácilmente el script exportado. Digamos que un nuevo gerente necesita los mismos permisos que un usuario existente pero con algunos permisos adicionales. Puedes crear el script T-SQL para el usuario existente y luego usar buscar y reemplazar para modificar el script para el nuevo gerente:

$LarsPermsFile = 'C:\temp\SQL2016N2-Lars-Fadetoblack.sql'
$ManagerPermsFile = 'C:\temp\SQL2016N2-Manager-Fadetoblack.sql'

Export-SqlUser -SqlInstance SQL2016N2 -FilePath $LarsPermsFile -User UlrichLars -Databases Fadetoblack

$ManagerPerms = Get-Content $LarsPermsFile

## reemplazar permisos
$ManagerPerms = $ManagerPerms.Replace('DENY INSERT ON [dbo].[Finances]','GRANT INSERT ON [dbo].[Finances]')
$ManagerPerms = $ManagerPerms.Replace('DENY SELECT ON [dbo].[RealFinances]','GRANT SELECT ON [dbo].[RealFinances]')
$ManagerPerms = $ManagerPerms.Replace('UlrichLars','TheManager')

Set-Content -path $ManagerPermsFile -Value $ManagerPerms

En este ejemplo, exportamos los permisos para el usuario “UlrichLars” de la base de datos “Fadetoblack” y lo guardamos en $LarsPermsFile. Luego, leemos el contenido del archivo y lo modificamos para otorgar permisos adicionales al nuevo gerente. Finalmente, guardamos el script modificado en $ManagerPermsFile.

Puedes usar un editor de texto como Visual Studio Code para comparar los dos archivos y ver las diferencias. Esto facilita la revisión y asegura que las modificaciones sean correctas.

Exportar permisos de usuario utilizando el comando Export-SqlUser no está limitado a una versión específica de SQL Server. Puedes usar el parámetro DestinationVersion para generar el script compatible con diferentes versiones de SQL Server:

Export-SqlUser -SqlInstance SQL2016N2 -Databases FadetoBlack -User TheManager -FilePath C:\temp\SQL2016N2-Manager-2000.sql -DestinationVersion SQLServer2000
notepad C:\temp\SQL2016N2-Manager-2000.sql

Este comando exporta los permisos para el usuario “TheManager” de la base de datos “FadetoBlack” y lo guarda como un script compatible con SQL Server 2000.

En conclusión, el comando Export-SqlUser del módulo dbatools es una herramienta poderosa para exportar permisos de usuario de SQL Server. Simplifica el proceso y ahorra tiempo en comparación con escribir el script T-SQL equivalente manualmente. Además, permite modificar fácilmente el script exportado para adaptarse a diferentes permisos de usuario. Ya sea que necesites exportar permisos para auditoría, transferir a otra base de datos o crear nuevos usuarios, el comando Export-SqlUser es un activo valioso en tu conjunto de herramientas de administración de SQL Server.

¡Feliz automatización!

Nota: Los ejemplos de código en esta publicación del blog fueron escritos utilizando la versión 0.8.942 de dbatools. Ten en cuenta que puede haber cambios importantes o nuevos comandos en futuras versiones de dbatools. Asegúrate de consultar la documentación y actualizar el módulo en consecuencia.