Change Database Ownership

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

image

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

Leave a Reply

Your email address will not be published. Required fields are marked *