SQL Server Administration by Powershell (Part 2)

The estimated reading time for this post is 1 minutes

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 = "Fard\SQLAGENT" 
$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

 

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz