Archive

Archive for the ‘Change Database Ownership’ Category

Change Database Ownership

November 11, 2014 Leave a comment

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