Change TERMSERV logins in Windows Credential Manager

This script will delete and then re-add windows credential manager entries via the cmdkey command line utility. I use this when I have to change windows account login to update all the termserv entries so I don’t have to save them all again. Use at your own risk.

cls
$objects = @();
$select = @('Target:', 'Type:', 'User:')
[int]$counter = 0;

$obj = New-Object -TypeName PSObject -Property @{
    Target = ''
    Type = ''
    User = ''
}

$creds = cmdkey /list | Select-String $select
$creds | %{
    $counter++;

    [string]$line = $_.ToString()

    switch -Wildcard ($_){
        "*Target:*"{
            $obj.Target = $line.Substring($line.IndexOf(':')+2, $line.Length - $line.IndexOf(':')-2)
            break;
        }
        "*Type:*"{
            $obj.Type = $line.Substring($line.IndexOf(':')+2, $line.Length - $line.IndexOf(':')-2)
            break;
        }
        "*User:*"{
            $obj.User = $line.Substring($line.IndexOf(':')+2, $line.Length - $line.IndexOf(':')-2)
            break;
        }
        
    }

    if($counter % 3 -eq 0){
        $objects += $obj;
        $obj = New-Object -TypeName PSObject -Property @{
            Target = ''
            Type = ''
            User = ''
        }
    }

}

$userName = 'domain\login'
$password = 'password'

$objects | where{$_.Target -like "*TERMSRV/*"} | %{
    $string = $_.Target
    $string = $string.Substring($string.LastIndexOf("=")+1)
    $cmd = "cmdkey /delete:$string"
    Invoke-Expression -Command $cmd
    $cmd = "cmdkey /add:$string /user:$userName /pass:$password"
    Invoke-Expression -Command $cmd
}

Expand Windows Groups on Sql Server

Quick post on how to expand windows groups to show their sub-groups and logins in sql server. This just writes the group hierarchy to the console. Use at your own risk.

import-module activedirectory

function Get-GroupHierarchy{
    param(
        [Parameter(Mandatory=$true)]
        [String]$searchGroup
    )
    $outputs = @();
    [int]$i++ | out-null;

    get-adgroupmember $searchGroup | sort-object objectClass -descending | %{
        $output = new-object -TypeName PSObject -Property @{
            Parent = $searchGroup
            GroupName = $_.Name
            Type = $_.objectClass
            Hierarchy = $i
        }
        $outputs += $output

        if($_.ObjectClass -eq 'group'){
            $outputs += Get-GroupHierarchy $_.name
        }
    }
    return $outputs;
}

cls
$srvName = 'ServerName'
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $srvName
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$ignoreGroups = @('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT');

$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup -and $_.Name -notin $ignoreGroups} | %{
    $loginName = $_.Name.Replace('TRX0\', '')
    Write-Host "Windows Group:  $loginName" -ForegroundColor Green
    Get-GroupHierarchy $loginName | ft -AutoSize   
    Write-Host "`n`r"
}

Calling sp_start_job via Linked Server

If you’re trying to execute sp_start_job via a linked server and keep getting the error:

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name (‘InsertJobNameHere’) does not exist.

,then here are the steps you need to follow to resolve the issue:

First, take stock of your life and ask yourself why you’re doing this in the first place; use an SSIS package man!
Find the login that is being used for the linked server.
Really, really question whether this is the best option for accomplishing the task you’re trying to solve.
On the target server, add the login from step 2 to the msdb database on the target server.
Add the login to the SQLAgentOperatorRole & TargetServersRole on the target server.
Grant EXEC to sp_start_job stored procedure in MSDB to the TargetServersRole on the target server.

USE [msdb]
GO
CREATE USER [linkedServerLogin] FOR LOGIN [linkedServerLogin]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'TargetServersRole', N'linkedServerLogin'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'linkedServerLogin'
GO
GRANT EXEC ON sp_start_job TO [linkedServerLogin]