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