Changing the SQL Server Database Owner

Posted: 12 Şubat 2013 in SQL, SQL SERVER

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'

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Connecting to %s