Tuesday, April 14, 2015

Creating a SQL Server User Login using Windows PowerShell

Windows Power Shell is great at administering most things on Windows OS that includes SQL Server as well.

This post describes step-by-step of creating a login for SQL Server 2008 using the SQL Server Management Studio. The same procedure applies to other versions as well.
http://hodentekhelp.blogspot.com/2008/09/how-do-i-create-new-login-for-sql.html

The following shows how you may do this using Power Shell.

Step 1:
In order to do create a login using Power Shell either use an existing Windows User or create a new user for Windows. You can create a new user to Windows desktop or Laptop as shown here.
http://hodentekhelp.blogspot.com/2015/04/how-do-you-create-windows-user.html

Step 2:
Launch Power Shell for SQL Server by executing SQLPS. If you want to know how to do this reivew this link here:
http://hodentekmsss.blogspot.com/2014/08/how-do-you-use-sql-server-powershell.html

Step 3:
After you complete the above step you should be on this line in your Power Shell console (The computer is running Windows 8.1 Profesisonal and the version of Power Shell is 4.0).

Microsoft SQL Server PowerShell
Version 11.0.2100.60
Microsoft Corp. All rights reserved.

PS SQLSERVER:\>

Now declare an instance of SQL Server using the following:

PS SQLSERVER:\> $server = new-Object Microsoft.SqlServer.Management.Smo.Server('Hodentek8\RegencyPark')

Now declare a user UserX using New-Object.. syntax.

PS SQLSERVER:\> $SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login ('Hodentek8\RegencyPark','Hodentek8\UserX')

Here Hodentek8(host) is the name of the computer; RegencyPark is the name of the instance and UserX is the name of the user.

Choose Windows Authentication using the following command and create the login using the following:

PS SQLSERVER:\>SqlUser.LoginType='WindowsUser'

At this point you should have the computer user called UserX for your computer otherwise you will get this error.
-------------
PS : A positional parameter cannot be found that accepts argument '$null'.
At line:1 char:3
+ PS <<<<  SQLSERVER:\> $SqlUser.LoginType='WindowsUser'
    + CategoryInfo          : InvalidArgument: (:) [Get-Process], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.GetProcessCommand

------------------------
Now create this login by this:

PS SQLSERVER:\>$SqlUser.create()

This completes the steps to create a New User Login which you can verify in SQL Server Management Studio as shown here:

















Some interesting posts can be found here:
http://hodentekPlus.blogspot.com