Create SQL Server User and Add Role via Powershell

I found a need to create a new SQL Server user, but wanted to automate it. The following script seemed to do the trick for me. You’ll have to install two modules, but the script will take care of that for you as well.

Set-PSRepository -Name PSGallery -InstallationPolicy Trusted  
Install-Module sqlserver -Confirm:$False  
Install-Module dbatools -Confirm:$False  
Import-Module sqlserver  
Import-Module dbatools

$server = "COMPUTERNAMESQLEXPRESS"
$user = "testuser"
$LoginType = "SqlLogin"
$pass = ConvertTo-SecureString -String "MyPlainTextPassword" -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential  
$svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
$svrole = $svr.Roles | where {$_.Name -eq 'sysadmin'}
$svrole.AddMember($user)

Leave a Reply

Your email address will not be published.