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:

Welcome to PowerShell for DBAs.com

by dbaduck 8. June 2011 19:30

It is a great day. I have wanted to do this for many years.  This site will be a consolidation of content made by me, and there will be other posts that will include videos of HOW TO.  Many other possibilities exist.

I decided to create this site when I went looking for PowerShell scripts and information and found that there is a lot of information coming out, but nothing really specific to DBA tasks.  I saw an opportunity to put content out that is completely related to DBA tasks and operations using PowerShell.

I have fulfilled many roles, in development, DBA, operations, etc. as well as Architecture, and Management.  I started in development, automating reports done while I worked in a prison, so I did not want to hang around until the 4GL language library finished.  So I took a C++ library and used it to produce the reports while I was at home each night.  That really made me jazzed, so I continued to automate many things with code.  So I was a scripter as much as I got the chance to be so.

I was recently awarded SQL Server MVP on January 1st, 2011 for my contributions to the Community. I am honored to be among them, and hope to provide the value that they all provide to the Community.  I post on Experts-Exchange.com as dbaduck and have a blog on SQLBLOG at http://sqlblog.com/blogs/ben_miller

Hold on to your hats, and get revved up and pull out the PowerShell tools enough to have fun doing some DBA tasks and your life will be enhanced and more efficient.

Tags:
Categories: General