SQL command to change the database owner

To change the database owner using a sql command you can use the ALTER AUTHORIZATION command.

---------------------------------------------
alter authorization on database::[database_name] to new_owner;
---------------------------------------------

If you wanted to change the database owner on all the user databases on the sql server at the same time you could do something like this:

---------------------------------------------
declare @dbname varchar(128), @sql varchar(max), @owner varchar(3);
set @owner = 'sa' --- the intended databse owner
declare cursor1 cursor for 
select rtrim(name) from sys.databases 
   where databasepropertyex(name, 'Status') = 'ONLINE' 
   and name not in ('master', 'model', 'tempdb', 'msdb', 'distribution');

open cursor1;
fetch next from cursor1 into @dbname;

while @@fetch_status = 0
begin
   set @sql = 'alter authorization on database::' + quotename(@dbname) + ' to ' + @owner + ';';
   exec(@sql);

   fetch next from cursor1 into @dbname;
end
close cursor1;
deallocate cursor1;
---------------------------------------------

Leave a comment