Changing db owner script

• kshurtl

  SSC Enthusiast

  Points: 112

  More actions

  December 31, 2014 at 6:55 am

  #1767176

  This is the proper syntax for the sp_MSforeachdb. I've tested this successfully in SQL 2008 R2, but it should be good for 2005 on up.

  EXEC sp_MSforeachdb

  '

  EXEC [?].[dbo].sp_changedbowner @loginame = N''sa'', @map = ''false''

  '

  You can also add or remove selectively. The below shows NOT IN and will change all databases except those listed, but you can of course use IN instead if you only want to change a specific few that you know the names of:

  EXEC sp_MSforeachdb

  '

  IF ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')

  EXEC [?].[dbo].sp_changedbowner @loginame = N''sa'', @map = ''false''

  '

• Jon.Morisi

  SSChampion

  Points: 12846

  More actions

  May 7, 2015 at 4:38 pm

  #1796433

  You could just add some string concatenation to build the statements you need to run off of this:

  select name, suser_sname(owner_sid) as owner, [Put your string concatenation here] AS [Statement to Run]

  FROM sys.databases

  where suser_sname(owner_sid) <> 'sa'

  and database_id > 4

  You could even run this from registered servers and get it all dumped in one go.

  HTH,

  Jon

• kshurtl

  SSC Enthusiast

  Points: 112

  More actions

  May 31, 2016 at 4:52 pm

  #1882305

  This will show errors for "Cannot change owner..." as mentioned above for the master, model, tempdb, and distribution databases, but it will run and successfully update all other databases despite the error. If you want to code around it to avoid the errors, or logically only update the databases that aren't already 'sa' you can do so.

  EXEC sp_MSforeachdb

  '

  EXEC [?].[dbo].sp_changedbowner @loginame = N''sa'', @map = ''false''

  '

• gfoxxy93

  Right there with Babe

  Points: 776

  More actions

  June 1, 2016 at 10:39 am

  #1882547

  Modify this powershell script to loop through an array of servers and then db list.

  $SQLAdminUser = 'AdminUser'

  $SQLAdminPwd = get-content "PathToSecuredCredentials.txt" | convertto-securestring

  $srvConn = New-Object('Microsoft.SqlServer.Management.Common.ServerConnection') ('ServerName', $SQLAdminUser, $SQLAdminPwd)

  $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srvConn

  $srv.Databases | where{$_.Name -eq 'DatabaseName'} |

  foreach {

  $_.SetOwner('sa')

  $_.Alter();

  }