Big Data, Data Analytics and Data Science | BI-BIGDATA.com

This is PowerShell tutorial script to walk you through how to create database login with the help of PowerShell and SMO.

Here is simple way was can achieve this with the help of T-SQL:

CREATE LOGIN [bi-bigdata]
WITH PASSWORD=N'YourPasswordAsPerPolicy',
CHECK_EXPIRATION=OFF
GO

But this of that we do not have SQL Server Management Studio with you and you want to create login or another scenario where you are going to create database login with some sort of automated script. Following simple PowerShell script is really going to help you out there.

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server  = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#Specify Database Login Name
$Login_Name = "bi-bigdata"

#Drop Login if exists
if ($Server.Logins.Contains($Login_Name))
{
$Server.Logins[$Login_Name].Drop()
}

#Create an SMO Login object
$Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $Server, $Login_Name

#identify what type of login this is.
#The possible LoginTypes are AsymmetricKey, Certificate, SQLLogin, WindowsGroup, and WindowsUser
#We are using using a SQLLogin for this turorial
$Login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin

#Login object also has a few properties, such as PasswordPolicyEnforced and PasswordExpirationEnabled.
$Login.PasswordExpirationEnabled = $false

#Prompt the user for the password instead of hardcoding it
#collect Password using a Read-Host cmdlet.
$Password = Read-Host "PW" –AsSecureString

$Login.Create($Password)

That’s it. In next article I’ll write about how to assign permissions and roles to a login existing database login.

Stay tuned…

Sandip