Add Windows User to local SQL Server with PowerShell

Question

I would like to add an existing local user to the SQL Server as a sysadmin, with PowerShell. fter some research I have the following script so far:

$Username = "JohnDoe"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
$SqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "localhost"
$SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlServer, "$Username"
$SqlUser.LoginType = 'WindowsUser'
$SqlUser.Create()
$SqlUser.AddToRole('sysadmin')

The user exists on localhost, it is member of the Users and the Administrators group. I got the following error message:

Exception calling "Create" with "0" argument(s): "Create failed for Login 'JohnDoe'. " At C:\Users\LocalAdmin\Desktop\try.ps1:7 char:16 + $SqlUser.Create <<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

Exception calling "AddToRole" with "1" argument(s): "Add to role failed for Login 'JohnDoe'. " At C:\Users\LocalAdmin\Desktop\try.ps1:8 char:23 + $SqlUser.AddToRole <<<< ('sysadmin') + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

Windows Server 2008 R2 with SQL Server 2008 R2 What am I doing wrong or what am I missing?

EDIT: Updated the script based on the suggessions from C.B. and mortb, but still not working. I have updated the script above to the current state, and the error message with that one what I am getting now.

Answer 1

I did not try your code. But, the following one worked for me on my SQL Express instance.

    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $env:ComputerName
$conn.applicationName = "PowerShell SMO"
$conn.ServerInstance = ".\SQLEXPRESS"
$conn.StatementTimeout = 0
$conn.Connect()
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $conn
$SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $smo,"${env:ComputerName}\JohnDoe"
$SqlUser.LoginType = 'WindowsUser'
$sqlUser.PasswordPolicyEnforced = $false
$SqlUser.Create()

Answer 2

Change

'$Username'

with

"$Username"

Note that in powershell variable aren't expanded in single quote, then '$Username' is take as literal and not for the value of the variable.