BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2000+

Changing the SQL Server Database Owner

Sometimes you need to change the owner of a SQL Server database, perhaps as a database moves from a test environment into a production system. Using a stored procedure, the database owner can be changed, giving the new owner the permissions of the role.

sp_changedbowner Stored Procedure

The sp_changedbowner stored procedure is a system stored procedure that allows the owner of a database to be modified. The new owner inherits all of the permissions that are attached to the ownership role. These privileges are removed from the previous owner, who then receives only permissions directly assigned to their user and remaining roles.

The simple syntax for the sp_changedbowner stored procedure requires that you specify the login name of the new owner. The owner for the current database is then changed to the specified login. Any user aliases that are mapped to the previous owner are re-mapped as aliases for the new owner. The following statement changes the current database's owner to "sa", transferring any existing aliases.

NB: Ensure that your query window is connected to the correct database or a test database before executing this command.

sp_changedbowner 'sa'

If you do not wish to re-map aliases to the new owner, a second parameter can be provided. By specifying "false" for this second parameter, you instruct SQL Server to drop any aliases for the previous login before transferring database ownership.

sp_changedbowner 'sa', 'false'
19 August 2008