#name of instance - either server, server\instance, or server,port
$InstanceName = ".\SQL2008R2"

#The database in which the synonyms that need to be changed reside
$DatabaseWhereSynonymsReside = "DBNAME"

#If the synonym base server needs to be changed - modify this value
$NewBaseServer = $null

#base database in which the synonym needs to point to
$NewBaseDatabase = "NEWDBNAME"


# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;

#Connect to the instance specified
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $InstanceName;

#set context to the database specified
$database = $srv.Databases["$DatabaseWhereSynonymsReside"]


<#
    initialize an blank array to hold the synonyms
    if an attempt is made to simply do a foreach over
    the synonyms collection, script will fail because
    the synonym is dropped within the foreach
#>
$Synonyms = @()

#Add each of the synonyms to the array
$database.Synonyms | %{$Synonyms += $_}

foreach($synonym in $Synonyms)
{
#Create the new synonym object to be saved to the server
    $NewSynonym = New-Object "Microsoft.SqlServer.Management.SMO.Synonym"
#These properties must be set before any others can be set
    $NewSynonym.Name = $synonym.Name
$NewSynonym.Parent = $database
$NewSynonym.Owner = $synonym.Owner
$NewSynonym.Schema = $synonym.Schema


<#
        these are the Base object properties
        changing these changes where the synonym will point to
        Only currently changes the base database 
        and possibly the base server if one is provided
        uses standard 4 part naming
#>
#server
    if($NewBaseServer -ne $null -and $NewBaseServer -ne "")
    {
$NewSynonym.BaseServer = $NewBaseServer
    }
else
    {
$NewSynonym.BaseServer = $synonym.BaseServer
    }
#database
    $NewSynonym.BaseDatabase = $NewBaseDatabase
#schema
    $NewSynonym.BaseSchema = $synonym.BaseSchema
#object
    $NewSynonym.BaseObject = $synonym.BaseObject


<#
        initialize an blank array to hold the permissions on the existing synonym
        applying permissions to a SQL object requires the ID property to be set
        This array stores the permissions so that they persist after the original
        synonym has been dropped
#>
$Permissions = @()
#Add the permissions to the array
    $synonym.EnumObjectPermissions() | %{$Permissions += $_}

#drop the old synonym from the database
    $synonym.Drop()

#creates the new synonym in the database
    $NewSynonym.Create()
#if this fails, the synonym is gone
    

#loop over the array that has the permissions in it
    foreach($Permission in $Permissions)
    {

#create a new objectpermissionset object for the specified type of this permission
        $PermissionSet = new-object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet($Permission.PermissionType)

<#
            SMO permissions cannot easily be applied dynamically
            due to the fact that the methods used are explicitly
            "grant", "deny", and "revoke".

            The switch below determines which the old permission was
            and replicates that onto the new synonym

#>
switch($Permission.PermissionState)
        {

"Grant"
            {
$NewSynonym.Grant($PermissionSet, $Permission.Grantee, $false)
            }
"GrantWithGrant"
            {
$NewSynonym.Grant($PermissionSet, $Permission.Grantee, $true)
            }
"Deny"
            {
$NewSynonym.Deny($PermissionSet, $Permission.Grantee)
            }
        }

#persist the permission change to the database object
        $NewSynonym.Alter()

    }

}