It’s a common issue with DBA’s to forget changing the database ownership after restoring the database.Below are couple of methods by which database ownership can be changed.
Scripts:
use master go EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'',''master'') EXEC [?]..sp_changedbowner ''sa''' go
Change database ownership for single database
use database_name go sp_changedbowner 'sa'
Change Database Ownership using Powershell
param([string] $Sqlserver) if (-not (Get-Module SQLPS)) {Import-Module SQLPS -WarningAction SilentlyContinue -DisableNameChecking} foreach($_sqlinst in $Sqlserver.Split(',')) { if ($_sqlinst -match "\") { Write-Host "SQL Server Instance : "$_sqlinst cd c: $_pspath="SQLSERVER:SQL" + $_sqlinst + "Databases" cd $_pspath -WarningAction SilentlyContinue DIR |Where-Object {$_.Status -eq "Normal"}| foreach-object {$_.SetOwner('sa'); $_.Refresh()} } else { Write-Host "SQL Server Instance : "$_sqlinst cd c: $_pspath="SQLSERVER:SQL" + $_sqlinst + "default" +"Databases" cd $_pspath -WarningAction SilentlyContinue DIR |Where-Object {$_.Status -eq "Normal"}| foreach-object {$_.SetOwner('sa'); $_.Refresh()} } }