Here’s a simple PowerShell SMO code that shows you How-To change a SQL user password. Keep in mind, SMO needs to be installed and the assemblies loaded before using this code.
To load SMO, you can:
1. Install the SQLPS Module (is using SQL Server 2012 “Denali”), or Chad’s Miller SQLPS module for SQL Server 2008/R2.
Import-Module SQLPS -DisableNameChecking
2. Or, Load the SMO Assemblies using the PowerShell V2 “AddType” command: (but carefull is you have multiple SQL Server versionsin the same box)
Add-Type -AssemblyName “Microsoft.SqlServer.Smo”
3. Or, use the still reliable the old PowerShell V1 load assemblies line using:
[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
In the following example show how to connect to a SQL Server using both the default Windows Authentication (with high Administrator privileges already set), or SQL Server Authenbtication to later change a user password.
*Hint*: If you want to include special characters, you need to use the single qoutes or PowerShell will think that it’s a variable name.
As you can see, with just a few line of PowerShell SMO code you can start orchestrating a solution that can be applied to your SQL Server environments.
Just try it!