WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, May 5, 2011

Finding all the databases with an owner other than SA

 Just an odd tidbit - If you use a standard DB owner and want to check to see that all databases are setup properly - you can use this Powershell script to find any DB that is not properly "owned".  Easy to change if you are searching for a specific owner by changing Where{$_.owner -ne "sa"} .  Enjoy!


Clear-Host
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$logFile = "C:\Powershell\Files\DBOwner_NonCompliant.txt"
if(Test-Path $logFile) {Remove-Item $logFile}

$server_list = @("Server\Merlot","Server\Cabernet")

foreach($sqlInstance in $server_list)
{
    $sqlInstance  | Out-File $logFile -Append

    $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance
    $sqlServer.Databases | Where{$_.owner -ne "sa"} | Select-Object name, owner,LastBackupDate,Size |
        Out-File $logFile -Append

}   

Invoke-Item $logFile

No comments:

Post a Comment