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'
);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.
Posts
