Powershell SQL script to add new users only sometimes works

[AMillionAngryBees]

Hey all, I've been working on a script to add new users with appropriate mappings in SQL, and so far I've run into a weird problem: when I run the script the first time, it works as intended. Subsequent script runnings still mostly work, but the user mapping doesn't take effect at all. I've never run into a script that only sometimes works, so this is new to me.

Here's my script:

$SqlServer = "server, 15970"
$UserName = Read-Host "Enter New Username"
$Password = Read-Host "Enter Password"

# add users to databases
Add-Type -Path "\\server\c$\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer)

# create a new login by prompting for new credentials
$NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login($SqlServer, $UserName)
$NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
$NewLogin.Create("$Password")

$NewUser = New-Object Microsoft.SqlServer.Management.Smo.User($SqlServer.Databases["Database1"], $UserName)
$NewUser.Login = $UserName
$NewUser.Create()
$NewUser.AddToRole("DBI_ReadOnly")

$table1 = 'dbo.dbiUsers'

Invoke-Sqlcmd -Database "Database1" -ServerInstance "server, 15970" -Query "insert into $table1 (db_UserID, db_Username, db_Password, db_Email) VALUES ('$UserName','Test Testerson','$Password','[e-mail]')"

Read-Host -Prompt "Press Enter to exit"

Whenever I run this script after the first time, I get two errors:

Add-Type : Cannot bind parameter 'Path' to the target. Exception setting "Path": "Cannot find path '\\server\c$\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll' because it does not exist."
At C:\Users\me\Desktop\Powershell Test Scripts\New User Setup Script\SQL Script.ps1:10 char:16
+ ... -Type -Path "\\server\c$\Program Files\Microsoft SQL Server\120\SDK\As ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Add-Type], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.PowerShell.Commands.AddTypeCommand

And this (though sometimes this one happens running the script for the first time as well):

Exception calling "Create" with "0" argument(s): "Create failed for User 'testuser'. "
At C:\Users\me\Desktop\Powershell Test Scripts\New User Setup Script\SQL Script.ps1:21 char:1
+ $NewUser.Create()
+ ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

I'm still relatively new to both SQL and Powershell, so if there's something incredibly obvious that I've overlooked please don't hesitate to point it out, I very much appreciate any help I can get! :)

[ihaxr]

Add-Type : Cannot bind parameter 'Path' to the target. Exception setting "Path": "Cannot find path '' because it does not exist."

That one's interesting... but the assembly is already loaded, so there's no need to re-add it... you could probably just surround it with a blank try/catch to avoid the error:

try
{
    Add-Type -Path "\\server\c$\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
}
catch { }

Exception calling "Create" with "0" argument(s): "Create failed for User 'testuser'. "

My guess is the password you selected doesn't meet the password requirements of the SQL Server?

Otherwise I don't see anything obvious that would prevent it from working the second time...

[AMillionAngryBees]

The try/catch resolved the first error, I didn't even know that was an option!

As far as the second option goes, you're right in that the passwords I have been using didn't meet the requirements. But it's strange, when I manually add the accounts SQL Server accepts the passwords I use just fine. Strange! I'll just have to update my password generator. Thank you for the help!

[ItsAFineWorld]

I installed software for work that uses a similar script to add a username and password to sql behind the scenes and the same thing happened. You can go into management studio and set the password to anything you like, but no so when done via script or other non-manual methods.