Como transferir roles de servidor entre instancias de SQL Server

Es necesario saber cómo transferir roles de servidor entre instancias de SQL Server cuando queremos mover una base de datos de un equipo a otro. Para no perder los permisos actuales asignados a los logins es recomendable generar un script con la asignación de estos permisos. Esto se puede hacer manualmente uno por uno o se puede usar el  script de este artículo para generarlos todos juntos.

Los roles de servidor también se las conoce como funciones de servidor.

Recientemente escribí un post que habla de cómo transferir los Logins entre instancias de SQL Server utilizando scripts documentados en la base de conocimiento de Microsoft.

Pueden encontrar este post en:

Cómo transferir los inicios de sesión y las contraseñas entre instancias de SQL Server

Sin embargo, uno de los lectores me hizo notar, que no había explicado que los scripts generados por estos documentos no siempre generan los correspondientes roles de servidor que tenían en la instancia de origen.  Solo el método 2 del documento en inglés que transfiere los Logins desde el SQL Server 2000 al SQL Server 2005/2008 genera el script para transferir los roles de servidor entre las instancias. Las otras versiones del script no lo hacen.

Solución

Si ya hemos creado los Login con el script de estos documentos de la base de conocimiento de Microsoft y debemos  además, transferir los roles de servidor a esta nueva instancia, y si nuestro origen es un SQL Server 2005 o un SQL Server 2008 o versión posterior, podemos usar el siguiente script para generar el script con la creación de los roles de servidor:

-- Genera el script con los Roles para los Logins
SELECT
      'exec master.dbo.sp_addsrvrolemember @loginame='''
      + sp.name + ''', @rolename=''' + sp2.name + ''';'
FROM  
      master.sys.server_principals AS sp
JOIN  master.sys.server_role_members AS srm
  ON  sp.principal_id = srm.member_principal_id
JOIN  master.sys.server_principals AS sp2
  ON  srm.role_principal_id = sp2.principal_id
WHERE 
      sp.type IN ('S', 'U', 'G')


-- Genera el script con la base de datos 
-- por default (omision) para los Logins
SELECT
      'ALTER LOGIN [' + sp.name
      + '] WITH DEFAULT_DATABASE = ['
      + sp.default_database_name + '];'
FROM	
      master.sys.server_principals AS sp
WHERE 
      sp.type IN ('S', 'U', 'G')
AND   sp.default_database_name IS NOT NULL