2

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 <> 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.

  • If you are using PowerShell why use sqlcmd, just use Invoke-Sqlcmd then it will be output as an data table. If you want just the output as strings just pipe it to select -ExpandProperty Fixusers. – Shawn Melton Mar 29 '16 at 20:17
1

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 <> 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.

  • Thanks Max! This did the trick with one tweak - I had to remove the QUOTENAME() in the ALTER USER statement so that sqlcmd could parse the query. – Paul Bell Mar 29 '16 at 19:43
  • What modification did you make? I tested the code above on my local 2012 instance using powershell, and it worked perfectly. – Max Vernon Mar 29 '16 at 20:05
  • SQLCMD.EXE : Sqlcmd: 'ALTER USER [ETLUser] WITH LOGIN = [ETLUser];': Unexpected argument. Enter '-?' for help. At line:1 char:7 + sqlcmd <<<< -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname + CategoryInfo : NotSpecified: (Sqlcmd: 'ALTER ... '-?' for help.:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError – Paul Bell Mar 29 '16 at 20:27
  • So I just changed the SELECT statement to "SELECT FixUsersCmd = 'ALTER USER ' + dp.name + ' WITH LOGIN = ' + sp.name + ';' – Paul Bell Mar 29 '16 at 20:28
  • If you have a user name like "some sql server" that code won't work. What version of sqlcmd.exe are you using? – Max Vernon Mar 29 '16 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.