Change database owner

I am about to join an internal course, and would like to prepare by installing the SQL Server Database Product Samples from CodePlex.
After the installation, the databases were owner by my login, and I would like to have the owner changed to "sa". I my case it is renamed, but that is another story.

Renaming a database in SQL Server Management Studio is done on the properties of the database in the page "Files".
When I ask for the change script by clicking the Script drop-down, I get a script that uses the stored procedure sp_changedbowner.
The article on this procedure in Microsoft documentation notes that this feature will be removed, and that I should use ALTER AUTHORIZATION instead.
Then I rewrote the statement to use the recommendation
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
Please recall that I have renamed "sa".

The samples generates several databases. Six in my case.
Instead of generating a script for each database, I made a script to generate these scripts
SELECT N'ALTER AUTHORIZATION ON DATABASE::[' + [databases].[name] + N'] TO [sql_sa];' AS [DCL]
FROM [master].[sys].[databases]
WHERE [databases].[owner_sid] != (
  SELECT [server_principals].[sid]
  FROM [master].[sys].[server_principals]
  WHERE [server_principals].[name] = N'sql_sa'
);

I could have generated the scripts for the first principal, as "sa" always is the first, but maybe another day I would like to use another principal.

The generated scripts are like this
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT] TO [sql_sa];

Dedicated Database Owner

By default the owner is the login that creates or restores the database. This is not a great situation to have a person as owner due to several security issues. Finding databases with the owner not being "sa" can be done with a script like below. To fence a database more in it can be assigned its own dedicated owner which is indicated in the column [expected_owner_name].

SELECT
	[databases].[name] AS [database_name],
	[server_principals].[name] AS [current_owner_name],
	[databases].[name] + N'_owner' AS [expected_owner_name]
FROM [master].[sys].[databases]
INNER JOIN [master].[sys].[server_principals] ON [databases].[owner_sid] = [server_principals].[sid]
WHERE [server_principals].[principal_id] > 1

The dedicated database owner is implemented as a SQL Login where the password is generated on creation. For that I use a script made by Pinal Dave and presented in this post. Creating the owner and the setting it can be done with a script like this:

-- Create owner
DECLARE @_login sysname = N'sqladmin_inventory_owner';  -- Copy from expected_owner_name
DECLARE @_database sysname = N'sqladmin_inventory';     -- Copy from database_name

DECLARE @_stmt_0 nvarchar(max) = N'CREATE LOGIN [' + @_login
	+ N'] WITH PASSWORD=''' + @_password
	+ N''', DEFAULT_DATABASE=[' + @_database
	+ N'],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;';
--RAISERROR( @_stmt_0, 0,0 ) WITH NOWAIT;  -- Debug
EXECUTE [master].[sys].[sp_executesql] @stmt = @_stmt_0;

DECLARE @_stmt_1 nvarchar(max) = N'ALTER LOGIN [' + @_login + N'] DISABLE;';
EXECUTE [master].[sys].[sp_executesql] @stmt = @_stmt_1;

-- Change database owner
DECLARE @_stmt_2 nvarchar(max) = N'ALTER AUTHORIZATION ON DATABASE::[' + @_database
	+ N'] TO [' + @_login + N'];';
EXECUTE [master].[sys].[sp_executesql] @stmt = @_stmt_2;

Using parameters with sp_executesql in a DCL statement fails with syntax errors. This is why I dynamically create the statement without using parameters. Not nice but working.

History

• 2025-05-23 : Section about Dedicated Database Owner added. Links changed from MSDN to current location.
• 2012-02-11 : Post created.