A database should not depend on one person's login remaining available forever. Finding databases owned by personal logins gives you a review list before account changes disrupt ownership-dependent behavior.
Find Databases Owned by Personal Logins Early
Database creation assigns an owner. On development and inherited servers, that owner can be the individual who ran the creation script. The setting survives long after the original work has moved to another team.
I inspect owner_sid alongside the resolved login name. A familiar displayed name is useful, but the stored security identifier is what defines the mapping. A renamed or removed login deserves more investigation than a text comparison alone.
The dbo user maps to the database owner. Ownership also affects code using EXECUTE AS OWNER and ownership-dependent access patterns. Changing the owner is therefore a security-context change, not merely a tidier catalog label.
Begin with a read-only inventory. Keep the instance identity and current values with any later change request. Database names repeat across environments, so a list without its source makes review unnecessarily uncertain.
SELECT name AS DatabaseName, owner_sid,
SUSER_SNAME(owner_sid) AS ResolvedOwner,
state_desc, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;An unresolved owner is a lead to investigate. Metadata visibility and Windows identity resolution can affect the displayed name. Verify the stored SID and relevant login configuration before declaring that every null represents a deleted account.
Use an Approved Owner List, Not a Name Guess
A name pattern cannot reliably distinguish a person from an operational identity. Define the logins approved to own databases on this instance. Review exceptions against that policy instead of assuming any account containing service is appropriate.
WITH ApprovedOwners AS
(
SELECT LoginName
FROM (VALUES (CONVERT(sysname, N'DatabaseOwnerLogin')),
(CONVERT(sysname, N'AnotherApprovedOwner'))) AS v(LoginName)
)
SELECT d.name AS DatabaseName,
SUSER_SNAME(d.owner_sid) AS CurrentOwner,
CASE WHEN SUSER_SNAME(d.owner_sid) IS NULL THEN N'Unresolved owner'
ELSE N'Owner outside approved list' END AS ReviewReason
FROM sys.databases AS d
LEFT JOIN ApprovedOwners AS a ON a.LoginName = SUSER_SNAME(d.owner_sid)
WHERE d.database_id > 4 AND a.LoginName IS NULL
ORDER BY d.name;Replace the illustrative names with your actual approved list. This query identifies exceptions; it does not prove they are personal logins. Have the database and security owners classify the returned accounts.
Do not choose the SQL Server service account automatically. Service execution identity and database ownership are different roles. Likewise, an sa-style administrative account is not a universal recommendation for every application's ownership design.
A high-privilege owner can increase the consequences of unsafe impersonation or TRUSTWORTHY settings. Review those relationships before selecting the replacement. Keep database ownership separate from routine application access wherever the design permits.
Check Which Code Uses Owner Context
Inspect modules running under an explicit execution context. In sys.sql_modules, execute_as_principal_id equal to negative two identifies EXECUTE AS OWNER. Resolve other explicit database principals to understand their separate mappings.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName,
OBJECT_NAME(m.object_id) AS ModuleName,
m.execute_as_principal_id,
CASE WHEN m.execute_as_principal_id = -2 THEN N'OWNER'
ELSE USER_NAME(m.execute_as_principal_id) END AS ExecutionContext
FROM sys.sql_modules AS m
WHERE m.execute_as_principal_id IS NOT NULL
ORDER BY SchemaName, ModuleName;
SELECT name, sid, type_desc
FROM sys.database_principals
WHERE name = N'dbo';Run this inspection in each affected database. A server-level owner list does not describe the modules inside them. Also inspect dependencies and application tests for operations reaching another database or server resource.
I test those owner-context modules on a copy before approving the change. A normal SELECT under an administrator account does not reproduce the application's effective context. Use representative application permissions and operations during that test.
Changing ownership does not transfer every object's schema ownership or rewrite every explicit permission. It changes the database owner and related dbo mapping. Keep those distinct levels visible when explaining the intended action.
Generate the Selected Ownership Change
Use ALTER AUTHORIZATION with an existing chosen login. QUOTENAME protects database and login identifiers when generating text. Confirm the target login exists before presenting commands as ready for execution.
DECLARE @ChosenOwner sysname = N'DatabaseOwnerLogin';
IF SUSER_ID(@ChosenOwner) IS NULL
THROW 50010, 'The approved replacement login must exist first.', 1;
SELECT N'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(d.name)
+ N' TO ' + QUOTENAME(@ChosenOwner) + N';' AS ReviewedCommand
FROM sys.databases AS d
WHERE d.name = N'OwnershipDemo'
AND d.database_id > 4;This script returns one named database's command for review. It does not change ownership. With the placeholder login name, the guard stopped my test run with error 50010, as intended. Replace the example names with the approved database and replacement login, then keep the exact before SID with the change record.
Executing the reviewed ALTER statement requires suitable permissions. Do not grant broad server privileges merely to make a one-time owner change convenient. Use the established administrative process and preserve its approval record.
If the target database is unavailable or part of a managed operational process, coordinate the change with its responsible team. An inventory does not grant permission to alter every database that appears in it.
Test Cross-Database Behavior Deliberately
Cross-database ownership chains depend on ownership and configuration, not only on an application's login name. Matching owners can affect whether ownership checks continue across databases. Changing one owner can therefore change behavior at that boundary.
Do not enable DB_CHAINING or TRUSTWORTHY as a shortcut when a test fails. Investigate the intended access design and permissions first. A broad configuration change can solve one symptom while widening the security exposure.
Which procedure assumes dbo represents a particular identity? Test that path explicitly, including any access outside the database. Keep success and failure cases rather than checking only the happy path under a privileged account.
For signed modules or other controlled permission designs, review the existing implementation with the security owner. Preserve the intended model rather than replacing it with a blanket ownership assumption. Ownership cleanup should leave access more understandable.
Recheck Databases Once Owned by Personal Logins
After the approved change, repeat the inventory for the selected database. Inspect dbo from inside it and run the representative application checks again. A successful ALTER message is only the start of verification.
SELECT name, owner_sid, SUSER_SNAME(owner_sid) AS CurrentOwner,
is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'OwnershipDemo';Compare the returned owner with the approved identity and retain the result. Ensure unrelated settings still match their before values. Keep the prior-owner information available if the rehearsal exposed a dependency needing more work.
Add ownership to database creation and restore intake reviews. Personal ownership can return whenever an administrator creates or restores another database. A recurring exception report catches that drift before an account departure makes it urgent.
A database owner should have a longer operational life than the person who happened to click Create. Give that responsibility an explicit identity, reviewed permissions, and tests showing what the change affects.
Check the replacement login's permissions independently of its ownership role. The owner maps to dbo in the selected database even when it lacks the server privileges of an administrative login. That distinction lets you reason about database access without assuming the identity needs authority over every database on the instance.
Document why the chosen identity belongs in the approved owner list. Databases owned by personal logins need an approved ownership decision, not a guessed replacement name. Preserve the original owner identifiers when reviewing databases owned by personal logins.
Related reading on this blog: Find Owner of Database: Change Owner of Database and Counting Users, Roles and Orphans in Every Database.
Database ownership is not a display name, it is a security context that applications can depend on.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
