Cómo codificar usuarios de bases de datos SQL Server con roles

Introducción

Una solicitud es recibida desde el equipo de desarrollo pidiendo copiar una de las bases de datos de producción al servidor DEV SQL para simular escenarios de prueba en tiempo real. Una vez que la copia de seguridad de la base de datos de producción es restaurada a la instancia DEV SQL, reemplazando la copia antigua, los antiguos usuarios de la base de datos DEV serán reemplazados por usuarios en línea. Pero aún necesitamos los antiguos inicios de sesión de la base de datos DEV para la conexión del sitio DEV.

¿Qué deberíamos hacer para respaldar estos inicios de sesión con sus permisos antes de reemplazar la antigua base de datos con la nueva de producción para restaurarla de nuevo?

Métodos de codificado de usuarios y permisos

Para poder conectarse a un servidor SQL, usted debe tener un acceso a nivel de servidor vía un inicio de sesión SQL Server o Windows. El usuario de la base de datos se mapea a un inicio de sesión existente con la propiedad SID de los inicios de sesión y los usuarios. Este mapeo funcionará si sus valores SID son idénticos. Un usuario huérfano es un usuario sin un Inicio de Sesión SQL correspondiente. Las bases de datos contenidas son una excepción donde los usuarios pueden conectarse a la base de datos sin un inicio de sesión al nivel del Servidor.

Los Inicios de Sesión SQL Server y los permisos están almacenados en las tablas del catálogo de seguridad del sistema en la base de datos maestra. Para listar estos inicios de sesión con sus roles asignados, usted necesita consultar estas tablas de sistema

En nuestro caso, una vez que la base de datos de producción es restaurada a nuestro Servidor DEV SQL, los usuarios de la antigua base de datos ya existen como inicios de sesión de servidor, pero no son mapeados a la base de datos ya que son reemplazados por usuarios en línea (tomando en consideración que los usuarios en nuestro sitio DEV difieren de los que están en línea).

Para mapear los permisos de los usuarios, tenemos las siguientes opciones:

1. Mapear manualmente los usuarios uno por uno, lo cual puede tomar mucho tiempo.
2. Usar T-SQL para generar estos usuarios y permisos desde la base de datos DEV antigua antes de la restauración, luego correr el script para mapearlos de nuevo después de restaurar la nueva copia de seguridad de la base de datos.

El script consiste en dos partes; la primera es para listar todos los usuarios de la base de datos excepto los integrados, con las sentencias CREATE USER:

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
		'U'
		,'S'
		)
	AND NAME NOT IN (
		'dbo'
		,'guest'
		,'sys'
		,'INFORMATION_SCHEMA'
		)

El resultado de esta consulta será así:

El segundo paso es generar la membrecía de roles de la base de datos para cada usuario con la sentencia EXEC sp_AddRoleMember:

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

Al correr la segunda consulta usted obtendrá el resultado a continuación:

Ahora usted tiene los scripts para crear cada usuario a nivel de la base de datos, mapearlo a su inicio de sesión correspondiente y otorgarle acceso añadiéndolo a los roles de la base de datos. El último paso es aplicar este script a su base de datos después del proceso de restauración.

Si usted ve el siguiente error cuando se está mapeando el acceso de los usuarios:

Error 15023: User or role ‘%s’ already exists in the current database.

Entonces, uno de los usuarios de DEV tiene el mismo nombre que el usuario en línea con diferente SID, ya que son creados en diferentes servidores SQL. Para arreglar este problema, use sp_change_users_login como sigue:

EXEC sp_change_users_login 'Auto_Fix'
	,'YourUser'
GO

Como puede ver, este método requiere un gran esfuerzo para generar el script T-SQL, y esto no funcionará en la situación de usuarios SQL Authentication debido al diferente SID para el mismo usuario generado en diferentes instancias SQL. También usted necesita tener un buen conocimiento de las tablas del sistema y los procedimientos almacenados para consultarlas y ejecutarlas para obtener información de los usuarios.

Para superar este problema y generar un script T-SQL para todos los usuarios con sus permisos, y generar los usuarios de SQL Authentication con su SID correcto, usaremos ApexSQL Script, una herramienta externa para codificar objetos de bases de datos y datos desde ApexSQL.

Usando ApexSQL Script

ApexSQL Script puede ser fácilmente usado para codificar los usuarios de la base de datos con los permisos con siguientes pasos:

1. Inicie ApexSQL Script
2. En la pestaña Select databases especifique el servidor que hospeda la base de datos que usted administra para codificar sus usuarios y el tipo de autenticación que usted usará para conectarse al servidor SQL. Si usted selecciona SQL Server authentication, usted necesitará especificar el nombre de usuario y la contraseña. Luego haga clic en Connect.

3. Una lista de todas las bases de datos hospedadas en su servidor serán mostradas en la cuadrícula derecha de resultados. Elija la base de datos de la cual tiene que codificar sus usuarios, la cual es SQLShackDemo en nuestro ejemplo:

   

4. En la pestaña Structure (ver a continuación), elija Permissions para codificar los usuarios con sus permisos.
   Usted puede seleccionar Logins with Users si usted necesita codificar la creación de inicios de sesión a nivel de servidor para los usuarios de la base de datos. En nuestro ejemplo, asumimos que los inicios de sesión ya existen en el servidor DEV, pero necesitamos tomar una copia de seguridad para los usuarios de la base de datos antes de reemplazar la antigua base de datos DEV con la nueva versión desde el servidor en línea.

5. Haga clic en Open.

   

6. Los componentes elegidos de la base de datos serán listados; usted puede seleccionar en esta lista aquellos usuarios que necesita codificar. Para codificar los usuarios con su membrecía de roles, seleccionaremos todos los usuarios y todos los roles en nuestro ejemplo aquí.

7. Haga clic en Script desde el menú Home

   

8. En la ventana Script wizard, elija T-SQL como el tipo de salida y haga clic en Next.

   

9. En la ventana Dependencies haga clic en Next.

10. En la ventana Output file, desde la lista desplegable Action, elija Create and write to file. Luego usted tendrá la opción de grabar el script a una ruta específica o abrirlo en un editor. Elija Open script in editor, luego haga clic en Create.

    

El script de resultado contiene las sentencias para crear los usuarios seleccionados y añadir estos usuarios a los roles de la base de datos que especifican sus permisos en la base de datos, y puede ser fácilmente ejecutado:

Si usted tiene un usuario de SQL Authentication común entre los ambientes DEV y en línea, usted puede codificar el inicio de sesión con el SID original eligiendo Logins with Users desde la ventana de estructura, y el resultado será así:

Conclusión

Como usted puede ver claramente, ApexSQL Script es una herramienta simple que nos ayuda con este caso de uso. Puede ser usado para codificar usuarios de base de datos con su membrecía en sus respectivos roles de base de datos en unos poco pasos sin la necesidad de estar familiarizado con las tablas de la base de datos del sistema y los procedimientos almacenados y la forma de llamarlos. La herramienta también puede superar las diferencias de SID de SQL Server Authentication entre diferentes instancias SQL codificando el usuario SQL Authentication con su SID desde el servidor fuente.

Enlaces útiles

• How to script encrypted SQL database objects
• Transfer SQL logins for users with a large number of SQL-authenticated logins
• Script a database for specific DML records only

Autor: Ahmad Yaseen

Traductor: Daniel Calbimonte