Category: Power Shell Script

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $Error.Clear() cls $servers = Get-Content E\LoginNames\Servers.txt $logins = @(login name to be dropped) foreach($server in $servers) { $srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server #drop database users foreach($database in $srv.Databases) { foreach($login in $logins) { if($database.Users.Contains($login)) { $database.Users[$login].Drop(); } } } #drop server logins foreach($login in $logins) { if ($srv.Logins.Contains($login)) { $srv.Logins[$login].Drop(); } } } $FilePath = “E:\LoginNames\” $OutFile = Join-Path -path $FilePath -childPath (“logins_” + (get-date).toString(‘yyyyMMdd_hhmmtt’) + “.log”)

Read More

$start = get-date write-host “Start: ” $start [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null ## Specify path of output file $FilePath = “D:\Version\” $OutFile = Join-Path -path $FilePath -childPath (“SQLVersions_” + (get-date).toString(‘yyyyMMdd_hhmmtt’) + “.log”) ## Output file name will be like “X:\Monitoring_Automation\ SQLVersions_20130906_1248AM.log” # Below is loop to generate version of each server @(foreach ($svr in get-content “D:\Version\Monitoring_servers.txt”) { $s = New-Object “Microsoft.SqlServer.Management.Smo.Server” $svr $s | select Name, Version }) | export-csv -noType $OutFile $end = get-date  write-host “End: ” $end

Read More