Author Posts

August 5, 2015 at 8:30 pm

I am using this script to script out windows server logins:
$timestamp = Get-Date -Format yyyy-MM-dd
$Srv = 'MyServer\MyInstance'
SL SQLSERVER:\SQL\"$Srv"\Databases
$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Srv;
$SQLInstance.name
$BatchSeperator = "`r`nGO" #$null for none, or "`r`nGO" for carriage return, line feed and batch seperator.

$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeDatabaseContext = $false;
$so.AllowSystemObjects = $false;
$so.IncludeDatabaseRoleMemberships = $true;
$so.IncludeIfNotExists = 1

$script = "USE [master]$BatchSeperator"

$WindowsLogins = $SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($so)} |% {$_.ToString()+$BatchSeperator};

$script = $script + "`r`n" + $WindowsLogins

$WindowsLogins text has the correct structure:
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'builtin\administrators')
CREATE LOGIN [builtin\administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\SYSTEM')
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

However after I concatenate it to $script variable:
$script = $script + "`r`n" + $WindowsLogins
I get this:
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'builtin\administrators')
CREATE LOGIN [builtin\administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\SYSTEM')
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO EXEC ...

As you can see the carriage return dissapeared from the string before "IF", this gives me a syntax error when I try to use this script.

Any ideas?

Thanks.

August 6, 2015 at 9:22 pm

Your $WindowsLogins variable will wind up containing an array of strings, instead of a single string, as written. When you expand an array in a string, it winds up separated by the default field separator (which is a space, unless you've changed the global $OFS variable.)

Here's how I'd tweak your code to accomplish the desired effect:

$timestamp = Get-Date -Format yyyy-MM-dd
$Srv = 'MyServer\MyInstance'
SL SQLSERVER:\SQL\"$Srv"\Databases
$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Srv;
$SQLInstance.name

$BatchSeperator = "`r`nGO" #$null for none, or "`r`nGO" for carriage return, line feed and batch seperator.

$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeDatabaseContext = $false;
$so.AllowSystemObjects = $false;
$so.IncludeDatabaseRoleMemberships = $true;
$so.IncludeIfNotExists = 1

$commands = @(
    'USE [master]'
    $SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($so)} |% {$_.ToString()}
)

$script = $commands -join $BatchSeperator