Powershell

Here are some scripts that I have found online and kept back for resuse or fine tuned them for specific tasks. Credit and thanks go to the original authors.


Allow PowerShell execution – Only has to be done once
## need to right click powershell and 'run as administrator'.
## choose yes when prompted.

Set-ExecutionPolicy unrestricted
Add a Local Admin
## Gives local admin rights to given user or AD group to a given machine.
## Useful if user is a local admin but group policy restricts access to the option.

## Replace DOMAIN, USER and MACHINENAME in below script.

$objUser = [ADSI]("WinNT://DOMAIN\USER")
$objGroup = [ADSI]("WinNT://MACHINENAME/Administrators")

$objGroup.PSBase.Invoke("Add", $objUser.PSBase.Path)
Display Local Admins on List of Machines
## Script to display a list of local admins for a given list of PCs / Servers.
## Useful for security review.  Can take into Excel and use the 'text to columns' feature.

$runTimeInfo = Get-Date -format "yyyyMMdd-HH:mm:ss"


## SET YOUR LIST OF MACHINES HERE
$strComputers = @("LGS17173",
"LGS18627",
"LGS16969"
)


## Loop through each computer in list above
foreach ($computerName in $strComputers)
{
    $computer = [ADSI]("WinNT://" + $computerName + ",computer")
    $Group = $computer.psbase.children.find("Administrators")
    $members = $Group.psbase.invoke("Members") | %{ $_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null) }
    
    
    ## Loop through each user for each computer in the parent loop
    ForEach ($user in $members)
    {
        $a = $computerName + "!" + $user.ToString()
        write-host $a
    }
    
}
Display SQL Services and Service Account
## Script to display accounts that sql services are running are on a given machine.
## Useful for troubleshooting permission/ environment issues.

## Set the $computerName variable and run.

$computerName = "LGS17173"

$Service_search = "SQL"
$matchlist = "reportserver|MsDtsServer100|MSSQLServerOLAPService|SQLSERVERAGENT|MSSQLSERVER"

get-wmiobject -ComputerName $computerName Win32_service | where { $_.name -match $matchlist } | format-table name, startname, startmode
Script out all SQL agent Jobs
## Powershell script to script out all sql agent jobs to files.
## Set the two variables below and run.

$sqlserver = "ldgldwdws01"  #sql server
$outputFolder = "c:\data\"  #needs to exist


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver
$jobs = $srv.JobServer.Jobs


ForEach ($job in $jobs)
{
    $jobname = $job.Name + ".Job.sql"
    $job.Script() | Out-File (join-path $outputFolder $jobname)
    write-host (join-path $outputFolder $jobname)
}
Script out all SSAS Databases on server
## Powershell to script out all SSAS databases on a server and save to .xmla files.
## Set the two variables below and run.


$serverName = "ldpldwrpt01" #ssas server
$outputFolder = "C:\data\"  #needs to exist


## load the AMO and XML assemblies into the current runspace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null
$dateStamp = (get-Date).ToString("yyyyMMdd")


## connect to the server
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)
{
    write-Host "Scripting: " $db.Name
    $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8)
    $xw.Formatting = [System.Xml.Formatting]::Indented
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw, $svr, $db, $true, $true)
    $xw.Close()
}
$svr.Disconnect()