sql - 使用PowerShell将Windows用户添加到本地SQL Server

Question

我想使用PowerShell将现有的本地用户作为sysadmin添加到SQL Server。经过研究，到目前为止，我有以下脚本:

$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')

该用户位于localhost上，它是Users and Administrators组的成员。我收到以下错误消息:

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和SQL Server 2008 R2
我做错了什么或我想念什么？

编辑:根据C.B.和mortb的建议更新了脚本，但仍然无法正常工作。我已经将上面的脚本更新为当前状态，并且错误消息中包含了我现在得到的错误消息。

Answer 1

我没有尝试您的代码。但是，以下示例对我的SQL Express实例很有效。

    $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()