Advertisements
Home > Business Intelligence > PowerShell for Creating a Database Login

PowerShell for Creating a Database Login

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

Advertisements
Categories: Business Intelligence
  1. August 29, 2013 at 1:40 pm

    Nice weblog right here! Also your web site
    so much up very fast! What web host are you the usage of?
    Can I get your affiliate link in your host? I wish my web site loaded up as
    fast as yours lol

  2. June 16, 2015 at 12:50 am

    How do i create multiple users in a database of sql server 2012

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: