#PSTip Adding local users to SQL Server Logins using SMO

Note: This tip requires PowerShell 2.0 or above.

There was a question on StackOverflow about adding local users to SQL Server logins. I provided an answer to that and realized from a user’s comment that the SMO method to create a SQL login works only in a specified manner.

So, in this tip, I will show you the SMO way of adding local users to SQL Server logins.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

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

In the above code snippet, observe the way we specified (line 10) the username for the login. It needs to be prefixed with the computer name and not just ‘localhost’.