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.

Try and remove the '' around '$Username' and see if it works? — mortb
– mortb, Commented May 17, 2013 at 13:34
Thanks, updated the script, but still not working. I have edited the question with the current script and the current error message. — Adam Szabo
– Adam Szabo, Commented May 17, 2013 at 13:42
I guess you need to disable password policy, if you have. I posted my answer. Check that. — ravikanth
– ravikanth, Commented May 17, 2013 at 14:05
No, not the password password policy, but the way the username is given. Username is only accepted in "${env:ComputerName}\Username" format. "localhost\Username" or "Username" is not accepted. I figured it out from your answer, many thanks! — Adam Szabo
– Adam Szabo, Commented May 17, 2013 at 14:25

ravikanth · Accepted Answer · 2013-05-17 14:04:42Z

6

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

answered May 17, 2013 at 14:04

ravikanth

26k4 gold badges62 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Szabo Over a year ago

Thank you. Turns out the problem was that the username is only accepted in "${env:ComputerName}\Username" format. "localhost\Username" or "Username" is not accepted.

Day Over a year ago

You'll need to use "$($smo.ComputerNamePhysicalNetBIOS)\Username" if you are not running the PowerShell on the same machine as SQL Server, but are connecting to a remote server

CB. · Accepted Answer · 2013-05-17 13:33:43Z

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.

answered May 17, 2013 at 13:33

CB.

61.3k9 gold badges171 silver badges165 bronze badges

4 Comments

Adam Szabo Over a year ago

Thanks, updated the script, but still not working. I have edited the question with the current script and the current error message.

CB. Over a year ago

try adding he server name in front ex: mysqlserver\JohnDoe

Adam Szabo Over a year ago

Just realized that basically your comment was right, it doesn't accept localhost but needs the hostname. Thank you.

Adam Szabo Over a year ago

No. My code works well as it is, the only thing I had to change is the format of the username.

David Brabant · Accepted Answer · 2013-05-17 14:12:23Z

1

function SQL-Get-Server-Instance
{
    param (
        [parameter(Mandatory = $true)][string] $DatabaseServer,
        [parameter(Mandatory = $true)][string] $InstanceName
    )

    if (!$InstanceName -or $InstanceName -eq "" -or $InstanceName -eq "MSSQLSERVER")
        { return $DatabaseServer }
    else
        { return "$DatabaseServer\$InstanceName" }
}


function Create-SQL-Login
{
    param (
        [parameter(Mandatory = $true)][string] $loginName,
        [parameter(Mandatory = $true)][string] $DatabaseServer,
        [parameter(Mandatory = $false)][string] $InstanceName = "MSSQLSERVER"
    )

    $sqlConnection = $null

    try
    {
        $Error.Clear()

        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "Server=$ServerInstance;Database=master;Trusted_Connection=True;"

        $Command = New-Object System.Data.SqlClient.SqlCommand
        $Command.CommandType = 1
        $Command.Connection = $sqlConnection
        $Command.CommandText = "create login [$loginName] from windows with default_database=[master], default_language=[us_english]"
        $sqlConnection.Open()
        $Command.ExecuteNonQuery() | Out-Null
        $Command.CommandText = "exec master..sp_addsrvrolemember @loginame = N'$loginName', @rolename = N'sysadmin'"
        $Command.ExecuteNonQuery() | Out-Null
    }

    catch
    {
        $str = (([string] $Error).Split(':'))[1]
        Write-Error ($str.Replace('"', ''))
    }

    finally
    {
        if ($sqlConnection)
            { $sqlConnection.Close() }
    }
}

And you call it like this:

Create-SQL-Login $env:COMPUTERNAME\JohnDoe $env:COMPUTERNAME

answered May 17, 2013 at 14:12

David Brabant

44k16 gold badges93 silver badges119 bronze badges

2 Comments

Adam Szabo Over a year ago

Many thanks for the effort, but it doesn't really show where is the error on my script. ravikanth's answer helped me to spot the problem though.

Dennis Over a year ago

This function will work with more modern versions of SQL Server as well.

Ayan Mullick · Accepted Answer · 2018-04-26 19:51:19Z

0

Found something simpler

Add-SqlLogin -ServerInstance <Server> -LoginName <User> -LoginType <LoginType> -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential ((New-Object ('Microsoft.SqlServer.Management.Smo.Server') "<Server>").Roles | where {$_.Name -eq 'sysadmin'}).AddMember("<User>")

answered Apr 26, 2018 at 19:51

Ayan Mullick

1622 gold badges16 silver badges40 bronze badges

Collectives™ on Stack Overflow

Add Windows User to local SQL Server with PowerShell

4 Answers 4

2 Comments

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related