Home > Change Database Ownership > Change Database Ownership

Change Database Ownership

The most common error dba do after restoring the database is they forget to change the database ownership.

Its really important to have the database ownership set to a user which will always be available to the SQL Server Instance

Script to change the database ownership for all the database on a instance

EXEC sp_MSforeachdb ‘EXEC [?]..sp_changedbowner ”sa”’

Change database ownership for single database

use <database_name>
go
sp_changedbowner ‘sa’

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()}
            }            
    }
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: