ALTER AUTHORIZATION on a Database: Find and Change the Owner

ALTER AUTHORIZATION on a database changes its owner, and SUSER_SNAME(owner_sid) finds the current one. Most databases never need attention here. The trouble starts when the owner login is gone or is the wrong one.

Where the Owner Is Stored

The owner of a database is one login, and it maps to the dbo user inside the database. The server keeps the owner as a SID, a binary identifier, in owner_sid. The function SUSER_SNAME turns that SID back into a name. When no login matches the SID, the function returns NULL. That NULL marks a database without a working owner.

In one client case, a database had no owner, and Management Studio could not open it. The fix was to find the owner and change it. The demo below uses a throwaway login and a throwaway database. The login password is a placeholder, and the login is disabled at once, so nobody can sign in with it.

USE master;
GO
IF DB_ID(N'OwnerCheckDemo') IS NULL CREATE DATABASE OwnerCheckDemo;
GO
IF SUSER_ID(N'OwnerCheckLogin') IS NULL
BEGIN
    CREATE LOGIN OwnerCheckLogin WITH PASSWORD = N'ReplaceWithYourOwnPassword1!', CHECK_POLICY = OFF;
    ALTER LOGIN OwnerCheckLogin DISABLE;
END;
GO

Find the Owner

This query lists the owner of the demo database. Remove the WHERE line to see every database on the server.

SELECT d.name AS DatabaseName, SUSER_SNAME(d.owner_sid) AS OwnerName, d.owner_sid
FROM sys.databases AS d
WHERE d.name = N'OwnerCheckDemo';

A new database belongs to the login that created it. That is rarely the owner you want on a production server. When that person leaves the company, the database stays owned by a closed account.

Change the Owner With ALTER AUTHORIZATION on a Database

ALTER AUTHORIZATION on a database takes the form ALTER AUTHORIZATION ON DATABASE::name TO login. It works in every supported version, and Microsoft recommends it over the older procedure. The next block makes the throwaway login the owner and reads the result. The statement needs the TAKE OWNERSHIP permission on the database. When the new owner is a login, it also needs IMPERSONATE on that login. A sysadmin has both.

ALTER AUTHORIZATION ON DATABASE::OwnerCheckDemo TO OwnerCheckLogin;
SELECT d.name AS DatabaseName, SUSER_SNAME(d.owner_sid) AS OwnerName FROM sys.databases AS d WHERE d.name = N'OwnerCheckDemo';

A Login That Owns a Database Cannot Be Dropped

SQL Server protects you from the worst case. If you try to drop a login that still owns a database, the statement fails. This is the rule that keeps most databases from losing their owner.

DROP LOGIN OwnerCheckLogin;

Msg 15174, Level 16, State 1, Line 1
Login 'OwnerCheckLogin' owns one or more database(s). Change the owner of the database(s) before dropping the login.

The rule keeps most databases from losing their owner. To catch the rest, check for databases where the owner name comes back NULL.

SELECT d.name AS DatabaseName, d.state_desc AS State
FROM sys.databases AS d
WHERE SUSER_SNAME(d.owner_sid) IS NULL
ORDER BY d.name;

On the test server the query returns no rows, which is the healthy answer. A row means a database with an owner nobody can find. Fix it with the statement above.

The NULL check has a blind spot. SUSER_SNAME returns a name while the login still exists in SQL Server. That holds even when the person behind a Windows login has left. A login for a deleted directory account stays enabled, so also ask your directory team who is still employed. This query adds the disabled logins.

SELECT d.name AS DatabaseName, sp.name AS OwnerLogin, sp.is_disabled
FROM sys.databases AS d
LEFT JOIN sys.server_principals AS sp ON sp.sid = d.owner_sid
WHERE sp.sid IS NULL OR sp.is_disabled = 1
ORDER BY d.name;

The demo database appears, because its owner login is disabled. Your server can list more rows. Each one is a database to review.

Pick the Right New Owner

Setting the owner to sa is one choice. It exists on every server, it never leaves the company, and it cannot be dropped. The name can be changed, so read it from the SID instead of typing it. The script builds the statement with SUSER_SNAME(0x01), runs it, and then drops the throwaway login.

DECLARE @sa sysname = SUSER_SNAME(0x01);
DECLARE @sql nvarchar(300) = N'ALTER AUTHORIZATION ON DATABASE::OwnerCheckDemo TO ' + QUOTENAME(@sa) + N';';
EXEC (@sql);
SELECT d.name AS DatabaseName, SUSER_SNAME(d.owner_sid) AS OwnerName FROM sys.databases AS d WHERE d.name = N'OwnerCheckDemo';
DROP LOGIN OwnerCheckLogin;

Now the login drops without an error. A sysadmin owner has one cost. Take a database marked TRUSTWORTHY. Code inside it can run with the owner rights, which is a known way to gain sysadmin power. A dedicated login with no other rights is the safer owner there.

The Older Procedure

The procedure sp_changedbowner does the same job. It is deprecated, which the server confirms by counting every call in its deprecated features counter. It changes the owner of the database you are in, so the context matters. Use ALTER AUTHORIZATION on a database in new scripts.

USE OwnerCheckDemo;
EXEC sp_changedbowner N'sa';
SELECT instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE N'%Deprecated Features%' AND instance_name LIKE N'%changedbowner%';

The counter row for sp_changedbowner is above zero after the call. The newer statement names its database, so no context can mislead you.

What an Orphaned Owner Breaks

The owner can leave and its account can go while the databases keep working. Agent jobs and endpoints have their own owners, so they do not depend on the database owner. List All Jobs With Owners in SQL Server Agent shows the job side.

The owner matters for the dbo mapping. It also matters for modules that run as owner, and for tools that read the owner name. Fixing it early costs one statement. A wrong owner also stopped an upgrade in msdb Upgrade Error 916: Fix the Database Owner.

You could argue that nobody looks at the owner, so why care. Nobody looks until an upgrade or a login drop fails because of it. A five minute review of the owner column prevents both.

What to Remember

Read the owner of a database with SUSER_SNAME(owner_sid), and treat NULL as a problem. Change it with ALTER AUTHORIZATION on a database, and prefer a stable owner. Clean up the demo when you finish.

USE master;
GO
ALTER DATABASE OwnerCheckDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE OwnerCheckDemo;

A database owner is not a formality, it is the one name the server falls back on.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.