Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.

Modifying SQL Server Synonyms

One task that often needs to be done when migrating a database to production for the first time is to modify the synonyms to point to whatever database is in production. There is no “alter synonym” command. The only way to change a synonym is to drop and create it.

Unfortunately, there are a couple of issues with doing this. The first is that the permissions disappear with a drop and create. If you are using the built-in groups (db_datareader/db_datawriter) or have granted permissions on the entire schema, this may not be a problem. However, if you have permissions as granular as the synonym, these permissions will have to be recreated.

The second issue is that the dependencies are lost. So, no longer is there knowledge within SQL Server that a procedure, view, or function relies on these synonyms.

This script below will not address the second issue. However, it will address the first – and provide a quick way of changing the database of a synonym. This script will build and execute the DROP, CREATE, and re-apply the permissions to the synonyms after switching them to a different database. The full object location could be changed with this script – just modify the REPLACE function.


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)

Comments

Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...