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

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)  

Adding Servers to SQL CMS (Central Management Server) using Powershell

One challenge I’ve had recently is getting a list of servers imported in to a CMS (Central Management Server). While this is easy if a CMS already existed, it isn’t so straight forward if this is the first time CMS has been used in an environment.

Enter Powershell and SMO. Like great warriors, Powershell and SMO seem to have a weapon for every occasion. On this occasion, the SMO RegisteredServers class comes in handy.

With the Powershell code below, you can take a pipe delimited list of AKA_Name|Instance in a text file and import those servers into the CMS. This could be quickly modified to import into a specific group within the CMS or to exclude the AKA_Name and make the name in the CMS the name of the Instance.

It’s nearly Christmas – so when the demands on your time get frightful, let it SMO, Let it SMO, Let it SMO!

$CMSInstance = "CMSSQLInstance"
$ServersPath = "C:\users\KyleNeier\PSSCRIPTS\allservers_w_Names.txt"
    
#Load SMO assemplies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"]

$Servers = Import-Csv -Delimiter "|" -Header "Name","Instance" -Path $ServersPath

foreach($Server in $Servers)
{
      #Put this in loop to deal with duplicates in list itself
      $AlreadyRegisteredServers = @()
      $CMSDBStore.GetDescendantRegisteredServers()|%{$AlreadyRegisteredServers +=$_.Name.Trim()}
      
      $RegServerName = $Server.Name
      $RegServerInstance = $Server.Instance
      
      if($AlreadyRegisteredServers -notcontains $RegServerName)
      {
            Write-Host "Adding Server $RegServerName"
            $NewServer = New-Object Microsoft.SqlServer.Management.REgisteredServers.RegisteredServer($CMSDBStore, "$RegServerName")
            $NewServer.SecureConnectionString = "server=$RegServerInstance;integrated security=true"
            $NewServer.ConnectionString = "server=$RegServerInstance;integrated security=true"
            $NewServer.ServerName = "$RegServerInstance"
            $NewServer.Create()
      }
      else
      {
            Write-Host "Server $RegServerName already exists - cannot add."
      }
}