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 – 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()

}

}

Comments

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

Loading comments...