You can change the owner of an MS SQL Server database using SQL Server Management Studio. Connect to your MS SQL instance, create a new query, and run the following Transact-SQL query:
ALTER AUTHORIZATION ON DATABASE::[yourdbname] TO [DOMAIN\user] GO
This is the recommended method.
Alternatively, you can use the built-in changedbowner procedure to change the owner:
USE [yourdbname] GO EXEC sp_changedbowner 'POWERADM\Alex' GO
This method is deprecated and will be removed in future versions of SQL Server. (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql?view=sql-server-ver16 ):
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER AUTHORIZATION instead.
To find out the current owner of a Microsoft SQL Server database, run the query:
USE [master] GO SELECT db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid where db.name='yourdbname'
Or you can list all databases and their owners:
SELECT [name], SUSER_SNAME(owner_sid) FROM sys.databases
You can also change the owner from the SSMS GUI:
- Open the database properties;
- Navigate to the Files tab;
- The current owner is listed here. To change it, click the button with three dots and select a new account from the Logins section.