Some time ago I created automatized installation of SharePoint. One of this set of scripts was script for configuration of clean MSSQL installation. Script uses SQL Server Management Objects (SMO) as API for MSSQL manangement:
# Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $connection
# Create login for SP2016\Administrator
$login = New-Object Microsoft.SqlServer.Management.Smo.Login $server, "SP2016\Administrator"
$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser
$login.Create()
$login.AddToRole("sysadmin")
# Create login for SP2016\SP_Admin
$login = New-Object Microsoft.SqlServer.Management.Smo.Login $server, "SP2016\SP_Admin"
$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser
$login.Create()
$login.AddToRole("dbcreator")
$login.AddToRole("securityadmin")
# Set max. server memory
$server.Configuration.MaxServerMemory.ConfigValue = 2048
$server.Configuration.Alter()
PowerShell script at the start loads SMO assemblies and connects to the local MSSQL instance. Then creates logins from domain accounts and add it to server roles. At the end the script set maximum memory which can MSSQL allocate for ist operation.
If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.