How to change the database owner in SQL Server

Who creates the database is the owner of the database. We can change the owner of the database using either sp_changedbowner or ALTER AUTHORIZATION. When we create a database, a row created in sys.databases.It stores information like database owner, creation date, size.. We can find out the database owner name either by using T-SQL query or SQL Server Studio Management:

Using T-SQL Query:

select suser_sname(owner_sid) from sys.databases where name

output:
How to change the database owner in SQL Server

Using SQL Server Management Studio:

Navigation: Right on the database and select properties.
How to change the database owner in SQL Server1

Change the database owner:

Using sp_changedbowner:
Using sp_changedbowner, we can only change the current database owner as shown in

Syntax:

use database_name
exec sp_changedbowner login_name

Example:
How to change the database owner in SQL Server2

Using the ALTER AUTHORIZATION: This feature available in SQL Server 2008 or later versions.

Syntax:

ALTER AUTHORIZATION ON DATABASE::database_name TO login_name

Example:

ALTER AUTHORIZATION ON DATABASE::k2tsql TO administrator

Related Posts

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools