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

[codesyntax lang=”powershell”]

#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

[/codesyntax]

SQL Server Service Account Change

[codesyntax lang=”powershell”]

#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)

[/codesyntax]

SQL Server Error Logs

[codesyntax lang=”powershell”]

#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

[/codesyntax]

SQL Server Failed Login Attempts

[codesyntax lang=”powershell”]

#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

[/codesyntax]

Mapped Users to Login and Databases

[codesyntax lang=”powershell”]

#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

[/codesyntax]

 

 

Share This Story

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

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan