Database Owner change : SQL Server

Database Owner change across SQL Server in the domain

SQL Server registers the owner of a database as the one who is creating the database. In an environment with lot of servers having database owners as the employees who has already left the organization. Many of the jobs were failing due to the database owner issue and we have to develop a script that needs to be run across the sql servers in our domain. Hope this helps others as well.

$findserversquery=@’select server from Server.dbo.servertable’@

$queryoutput= Invoke-Sqlcmd -ServerInstance "Server\Instance" -Query $findserversquery

foreach($data in $queryoutput)

{

$Server=$data.ServerName

$NamedInstance=$data.NamedIns

$Port=$data.Listenport

$owner=$Data.owner

$connectionstring = $Server+'\'+$NamedInstance+','+$Port

#connect to connection string using SMO

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | out-null

$smosvr = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $connectionstring

#find DB's from table which are under the owners mentioned above.

$finddatabases=

USE DBRepository

select r.name aS dbname FROM [REPO_DatabaseOwnershipTable]

where ServerName='$server' and NamedIns='$NamedInstance' and ListenPort='$Port' and Owner= '$owner'

#executing the script

$dbs=Invoke-Sqlcmd -ServerInstance "Server\Instance" -Query $finddatabases -QueryTimeout 65000 -ConnectionTimeout 65000

foreach($db in $dbs)

{

#database from tables

$Database=$Db.DBName

#database from SMO

$SMODatabases=$smosvr.Databases

foreach($db in $SMODatabases)

{

#Below one line will show the db and owner name for cross checking

$db.Name+','+$db.owner | where {$db.Name -eq $Database}

##SCript for changing the fetched DB's under the current owner.

#$db.SetOwner('sa')

#$db.Alter()

}