Create a SQL Server User using Powershell

Question

I've written a powershell script that creates a new sql server database and login, and then sets the database owner to the newly created user. This is successful. However, I get a login failed exception when attempting to login within the same script. If I use SQL Server Management Studio the login works.

Here's the script:

$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, 'TestDB')
$db.Create()

$login = new-object Microsoft.SqlServer.Management.Smo.Login("(local)", 'TestUser')
$login.LoginType = 'SqlLogin'
$login.PasswordPolicyEnforced = $false
$login.PasswordExpirationEnabled = $false
$login.Create('Password1')

$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $server.Databases.Item('TestDB')
$db.SetOwner('TestUser', $TRUE)
$db.Alter()
Invoke-Sqlcmd -ServerInstance localhost -Database 'TestDB' -Username 'TestUser' -Password 'Password1' -Query "SELECT * FROM sysusers"

I've tried adding a Start-Sleep (up to 5mins) to no avail, and I've tried Restart-Service mssqlserver -Force, also to no avail.

Any ideas?

Answer 1

This isn't an answer to the problem I was encountering, just a work around. The script is being run as part of an automated deployment, the overall scripts are run under the "NT AUTHORITY\SYSTEM" username, so to get around my logging in issue I'm simply using Integrated Security=true.