Modifying SQL Server Synonyms – this time with Powershell

So, how do you recover from the Holidays? Hot Cocoa by the fire? Relaxing evening with your spouse watching an easily predictable romantic-comedy? Snuggling up with SMO synonym class documentation?

I chose the SMO documentation. After I wrote the last blog post (http://sqldbamusings.blogspot.com/2011/12/modifying-sql-server-synonyms.html), I was saddened. I did not have a way to replicate the t-sql that I had used in Powershell. It’s been on my mind ever since, just couldn’t find the time to get it figured out. Everyone in this Neier household is exhausted from the joyous times had over the past few days and have crashed for the evening. I thought this to be a perfect time to turn this frown upside down and get some geek on.

My gift to myself is the script below. It uses SMO to enumerate all of the synonyms in a given database, drop the existing synonym, create a new synonym with the modified base database (and server if specified), and then replicate the permissions to the new synonym. I cannot think of ever having to change the schema or object name of a synonym when moving to production, so those are not included.

Here’s looking forward to an equally as eventful New Year’s weekend.

#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()
        
    }
    
}