SQL Server Administration by Powershell (Part 2)

PowerShell can help you to automate security tasks. Whether you need to monitor repeated failed login attempts by parsing out event logs, or manage roles and permissions, especially if the number of users in the system is very large, PowerShell can help you deliver.

In this blog, I am going to share some of SQL Server administrative tasks that can be accomplished  using PowerShell. PowerShell can help you to automate a lot of the repetitive, tedious, and mundane tasks that take many clicks to accomplish.

This blog post structure is by section and each section contains a specific Powershell script for specific task.

SQL Server Service Accounts

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name 
$instanceName = "SQLInstance" 
 
$Server= New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $instanceName
$Server.Services | Select Name, ServiceAccount, DisplayName, ServiceState | Format-Table -AutoSize

SQL Server Service Account Change

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
 
#replace this with your instance name 
$instanceName = "SQLInstance" 
 
$Server = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $instanceName
 
$sqlservice = $Server.Services | Where-Object Name -eq "SQLSERVERAGENT"
 
$username = "FardSQLAGENT" 
$password = "Pa$$w0rd" 
$sqlservice.SetServiceAccount($username, $password)

SQL Server Error Logs

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name 
$instanceName = "SQLInstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. 
Server -ArgumentList $instanceName
 
$server.ReadErrorLog() | Where-Object Text -Like "*failed*" | Format-Table –AutoSize

SQL Server Failed Login Attempts

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
 
#replace this with your instance name 
$instanceName = "SQLINstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. Server -ArgumentList $instanceName 
 
$server.ReadErrorLog() | Where-Object ProcessInfo -Like "*Logon*" | Where-Object Text -Like "*Login failed*" | Format-List

Mapped Users to Login and Databases

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
 
#replace this with your instance name 
$instanceName = "SQLINstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. Server -ArgumentList $instanceName 
 
$server.Logins
$server.EnumWindowsUserInfo()
 
$server.Databases | ForEach-Object {   #capture database object   $database = $_
 
$users = $_.Users
   $users |    Where-Object { -not($_.IsSystemObject)} |   Select @{N="Login";E={$_.Login}},   @{N="User";E={$_.Name}},   @{N="DatabaseName";E={$database.Name}},   @{N="LoginType";E={$_.LoginType}},   @{N="UserType";E={$_.UserType}} } | Format-Table -AutoSize

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

guest
0 Comments
Inline Feedbacks
View all comments

About The Author

Search Articles

Categories

Follow Fard Solutions