How to automatically fix Orphaned Users with PowerShell and SQLCMD I'm trying to properly capture output from SQLCMD in SQL Server 2012.

Here is the PS script:

$dbname = "MyDB"
$InstanceTarget = "localhost\SQLEXPRESS"

$FixOrphansSQL=@"
WITH login_CTE (name, type, sid)
As
(
Select name, type, sid from sys.database_principals where type = 'S'
)
select 'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid &lt;&gt; b.sid and a.name = b.name COLLATE Latin1_General_CI_AS 
GO
"@
$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname
sqlcmd -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname


If I run this

sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname


it works and the result is the script commands (on separate lines) to fix the orphaned users – but with some extra characters:

(lots of dashes -----------------)
ALTER USER InsightETLUser WITH LOGIN =InsightETLUser                                                                                                                                                                                     
ALTER USER APSDT_CDLinkedServerUser WITH LOGIN =APSDT_CDLinkedServerUser                                                                                                                                                                 

(2 rows affected)


When I run

$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname


nothing gets captured into $RepairOrphansSQL.
The "(lots of dashes -----------------)" you talk about are a result of the output from sqlcmd.  Instead of using SELECT in your script, you likely want to use PRINT with a cursor.  SELECT will generate a resultset that sqlcmd will print to the screen along with header info.  The dashes are part of this header info.  

The T-SQL won't work for logins/users that have spaces in their names, or other special characters such as dashes, etc.  To make it work correctly for those users/logins, you need to wrap the name in square brackets, using QUOTENAME().

Something like this:

$dbname = "test"
$InstanceTarget = "CP708-D377\MV2012"

$FixOrphansSQL=@"
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT FixUsersCmd = 'ALTER USER ' + QUOTENAME(dp.name) + ' 
    WITH LOGIN = ' + QUOTENAME(sp.name) + ';'
FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp ON dp.name = sp.name AND dp.sid &lt;&gt; sp.sid
    AND dp.type_desc = 'SQL_USER';
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
"@
$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname
echo $RepairOrphansSQL
if ($RepairOrphansSQL.Length -gt 0) {
    sqlcmd -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname
}


The above code checks if $RepairOrphansSQL actually contains any commands before running it; if the variable is empty, sqlcmd is not called.