Cuando se migra una base de datos a producción por primera vez, una tarea común es modificar los sinónimos para que apunten a la base de datos adecuada. Sin embargo, no existe un comando directo “alter synonym” en SQL Server. En su lugar, la única forma de cambiar un sinónimo es eliminarlo y recrearlo. Desafortunadamente, este enfoque viene con un par de problemas.

El primer problema es que cuando se elimina y recrea un sinónimo, se pierden los permisos asociados con él. Si ha otorgado permisos en todo el esquema o está utilizando los grupos incorporados como db_datareader/db_datawriter, esto puede no ser un problema. Sin embargo, si ha otorgado permisos tan detallados como el sinónimo en sí, estos permisos deberán ser recreados.

El segundo problema es que se pierden las dependencias del sinónimo. SQL Server ya no tendrá conocimiento de ningún procedimiento, vista o función que dependa de estos sinónimos. Esto puede llevar a problemas potenciales si estas dependencias no se abordan correctamente.

Para abordar el primer problema, podemos usar un script que eliminará y recreará los sinónimos mientras se preservan los permisos. Aunque este script no aborda el segundo problema de las dependencias, proporciona una forma rápida de cambiar la base de datos de un sinónimo.

Aquí hay un ejemplo de script que se puede utilizar para modificar la base de datos de un sinónimo:

DECLARE @DropSynonym NVARCHAR(4000), @CreateSynonym NVARCHAR(4000), @Permissions NVARCHAR(4000)

SELECT @DropSynonym = '', @CreateSynonym = '', @Permissions = ''

SELECT @DropSynonym = @DropSynonym + 'DROP SYNONYM ' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + '; ',
       @CreateSynonym = @CreateSynonym + 'CREATE SYNONYM ' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ' FOR ' + REPLACE(base_object_name, '[OldDB]', '[NewDB]') + '; '
FROM sys.synonyms;

WITH PermQuery AS (
    SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END COLLATE database_default AS PermissionState,
           perm.permission_name COLLATE database_default AS Permission,
           SCHEMA_NAME(obj.schema_id) AS SchemaName,
           obj.name AS ObjectName,
           CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END AS ColumnName,
           CASE WHEN perm.state <> 'W' THEN 'N' ELSE 'Y' END AS WithOption,
           usr.name AS UserName
    FROM sys.synonyms AS s
    INNER JOIN sys.all_objects AS obj ON s.object_id = obj.object_id
    INNER JOIN sys.database_permissions AS perm ON perm.major_id = obj.[object_id]
    INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
)

SELECT @Permissions = @Permissions + PermissionState + ' ' + Permission + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ' + ColumnName + ' TO ' + UserName + CASE WithOption WHEN 'Y' THEN ' WITH GRANT OPTION' ELSE '' END + '; '
FROM PermQuery

PRINT @DropSynonym
EXEC (@DropSynonym)

PRINT @CreateSynonym
EXEC (@CreateSynonym)

PRINT @Permissions
EXEC (@Permissions)

Asegúrese de reemplazar ‘[OldDB]’ con el nombre de la antigua base de datos y ‘[NewDB]’ con el nombre de la nueva base de datos en el script.

Al ejecutar este script, los sinónimos se eliminarán y recrearán con la base de datos actualizada. Además, el script volverá a aplicar los permisos a los sinónimos, asegurando que se mantenga el acceso necesario.

Es importante tener en cuenta que este script solo aborda el primer problema de preservar los permisos. Para manejar el segundo problema de las dependencias, pueden ser necesarios pasos adicionales. Se recomienda revisar y actualizar cualquier procedimiento, vista o función que dependa de los sinónimos después de modificarlos.

El uso de este script puede ayudar a agilizar el proceso de modificación de sinónimos en SQL Server, facilitando la migración de bases de datos a entornos de producción.