Create a SQL Login in PowerShell

by dbaduck 31. July 2011 07:40

Here is the inaugural post for this blog.  It will cover the concept and ability for you to create a Login.  I will also show you the alternate way using SQLPSX.  First off, you have to have the ingredients of the Login so that this will make sense.

To create a SQL Login, you have to have the following:

  • Server Name
  • Login Name
  • Login Type (Windows or SQL)
  • Password for the Login

Now for the documentation explanation of each parameter and the values they can be:

   1: <#   
   2:      $ServerName should be the SQL Server name and instance   
   3:          example:  "SQL01\INST01" or "SQL01"   
   4:         
   5:      $LoginName should be the login name you wish to have   
   6:          example: "dbaduck"   
   7:             
   8:      $LoginType should be whether or not you want Windows or SQL   
   9:          example: "WindowsUser" or "SqlLogin" or "WindowsGroup" or "Certificate"   
  10:                   or "AssymetricKey"  
  11:     
  12:      $Password is self explanatory for the Login  
  13:        
  14:      MSDN Article on 2008 SMO.Login  
  15:      http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.aspx  
  16:     
  17:  #>
 

The first part is to allow you to call the .ps1 file and pass it parameters:

   1: param (   
   2:     [String]$servername,   
   3:     [String]$loginname,   
   4:     [String]$LoginType,   
   5:     [String]$Password   
   6: )   
  

The above is a simple way, and I am going simple on purpose so that this can be leveraged by anyone at any level.

The next part is to actually use the PowerShell objects to create this.  It is a combination of PowerShell and SMO (SQL Server Management Objects).  Here goes the next part of the code:

   1: $login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $servername, $loginname   
   2: $login.LoginType = $LoginType   
   3: $login.Create($Password)

Notice in Line 1 that we are going to create an SMO.Login and pass it the Server Name and the Login Name as arguments to the New-Object function.  This is how you get the Login Object to create it in the SQL Server Instance.  After the LoginType is assigned to the Login Object, $login.Create is called and the parameter for that function is the Password.

The SQLPSX Way

SQLPSX is a library or “module” in PowerShell written by many developers, but the founder of it was Chad Miller.  This library has many items in it and one of them is to create a login for a SQL Server Instance.

The Parameter blog looks like this:

   1: param(   
   2:  [Parameter(Position=0, Mandatory=$true)] $sqlserver,   
   3:  [Parameter(Position=1, Mandatory=$true)] [string]$name,   
   4:  [Parameter(Position=2, Mandatory=$false)] [string]$password,   
   5:  [Parameter(Position=3, Mandatory=$false)] [Microsoft.SqlServer.Management.Smo.LoginType]$logintype='WindowsUser',   
   6:  [Parameter(Position=4, Mandatory=$false)] [string]$DefaultDatabase='master',   
   7:  [Parameter(Position=5, Mandatory=$false)] [switch]$PasswordExpirationEnabled,   
   8:  [Parameter(Position=6, Mandatory=$false)] [switch]$PasswordPolicyEnforced   
   9:  )

As you can see, there are a few more parameters than my simple example, but it a good contrast for what you can do.  I won’t post the code for the module function, but I will show you the command line that you may use to create one.

   1: <#   
   2:  .EXAMPLE   
   3:  Add-SqlLogin "Z002\sql2k8" "TestPSUnit" "SQLPSXTesting" 'SqlLogin'    
   4:  This command adds the TestPSUnit login to the Z002\sql2k8 server.   
   5: #>

 

I hope this helped and made sense to you all.  Happy Day to you in your next PowerShell endeavor.

DBADuck

Tags: