This blog post is following “Changing a SQL Server Login name with T-SQL“. Let’s take advantage of the .NET SMO framework assembly object model Namespaces with PowerShell to change a Windows account in a SQL Server Logins.
Remember to download the latest version of PowerShell.
There’s nothing wrong in using SSMS (SQL Server Management Studio) as our GUI application to manage our SQL Server engine. But soon, you will have the need to use PowerShell scripting for automating some daily tasks. And, Trust me! It will save you time.
Both Microsoft and the SQL Server Community provide you with some of the Awesome tools, such as the following PowerShell modules: SQLPS, SqlServer, Secretmanagement, and DBATools.
Let’s begin with creating a list all SQL users on our SQL Server using the DBATools module “Get-DBAuser” command:
Get-DBADBUser -SqlInstance 'localhost,1433'
As you can see, this command returns a lot of information you can export and dissect in many way.
Now, let’s take this a little further using SMO Object Model Namespaces.
Don’t be scare! in order to start using these SMO Classes. To start, all you need to have installed any of the following PowerShell Modules: SQLPS, SQLServer or DBATools, then execute the “import-Module” command:
## This will load SMO assemblies: Import-Module SqlServer
Then all necessary SMO Assemblies are loaded and ready to be consumed during your PowerShell session. You can start building your own PowerShell one-liners or scripts/functions command to interact with the SQL Server engine.
Let’s cut to chase, and create a simple PowerShell function “Get-SqlLogins‘ to simply list all my SQL logins in my SQL Server:
## - function_Get-SqlLogins.ps1:
function Get-Sqllogins
{
param
(
[parameter(Mandatory = $true)]
[string]$sqlname,
[string]$uname,
[string]$upwd
)
## - Prepare connection to SQL Server:
$SQLSrvConn = `
new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($sqlname, $uname, $upwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);
## - Get SQL SERVER list of database names:
$global:itm = 0
$SQLSrvObj.logins | Select-Object @{ l = 'itm'; e = { $global:itm; ++$global:itm }; }, name, logintype;
};
$sqlname = 'localhost,1433';
$uname = 'sa';
$upwd = '$SqlPwd01!';
Get-Sqllogins -sqlname $sqlname -uname $uname -upwd $upwd
## - End-of-File
Note: Save this code as function_Get-Sqllogins.ps1.
You can edit this file to run one liner at the time and explore the $SQLSrObj PowerShell object.
Use the following GET-Member(alias gm)command to explore the object content:
## - exploring .NET Objects: $SQLSrvObj | gm | Out-GridView
This is a good way to learn about your PowerShell objects. You’ll be surprised by the ton of information you can find for documentation.
Now, try listing all SQL logins names by typing the following:
## - shorthand to list all values in a object proprety: $SQLSrvObj.logins.name
So, with a few lines of code, you can quickly get results.
Now, proceeding with looking for the Windows account I want to change the name in the SQL Login.
For this, I need to add line numbers to the PSObject result. This way I can Isolate the Login ID:
$global:cnt = 0
$SQLSrvObj.logins | Select-Object @{ l = ‘cnt’; e = { $global:cnt; ++$global:cnt }; }, name, logintype
For the finale: Changing the SQL Login Name. I’m going to manually do this using SMO PowerShell One-liner:
I found that element #5 is the SQL login I need to change:
## - verify before making the changeto the SQL Login object; $SQLSrvObj.logins[5]
So far we’ve been working with SMO .NET Objects properties. Here’s where we use SMO .NET methods which affect the object (element#5) I have manually selected using “$SQLSrvObj.logins[5]“:
Last steps for updating the SQL Login name:
Note: Keeping in mind, the actual change starts at the Windows Account level.
1. The *.Alter() method sets the object ready to be changed:
$SQLSrvObj.logins[5].alter()
2. followup by the *.rename(**string**) method which will affect the name object.
$SQLSrvObj.logins[5].rename('MXTLPT01\Dev01')
3. And, finally we use the *.refresh() to update all logins list with the name change.
$SQLSrvObj.logins.refresh()
AS you can see, this open to some automation opportunities that can involve Windows Domain with SQL Server Accounts administration.
Don’t forget! always test your scripts. Practice makes a good scripter, and never be afraid of trying new stuff.
SQL PowerShell! It is the way!