Changing Object Ownership to DBO in SQL Server

Object ownership mismatches are one of those unglamorous SQL Server problems that surface at the worst possible times. A database migrated from a legacy system, a developer who created objects under their own login, a restore from an old backup - suddenly you've got stored procedures, views, and tables owned by accounts that may no longer exist, causing permission errors and broken application connections.

The fix is straightforward: change all object ownership to dbo. You can do this with a single script, or with the more modern approach using ALTER AUTHORIZATION. This article covers both methods, explains when each applies, and flags the gotchas you need to know before running anything in production.

Why Does Object Ownership Matter?

In SQL Server, every securable object has an owner. When ownership chains are broken - meaning an object is owned by someone other than dbo - SQL Server performs additional permission checks at each ownership boundary. This can cause queries to fail even when users have the correct permissions on the objects they're directly accessing.

The most common scenario: a developer creates a stored procedure under their own login. That procedure queries a table owned by dbo. When another user calls the procedure, SQL Server checks permissions at the ownership boundary between the procedure and the table. If the accounts don't match, access is denied - even if the user has EXECUTE on the procedure.

Beyond permission chains, orphaned object owners cause real operational problems. If the owning login is dropped from the server, objects owned by that login become inaccessible or throw errors. This is a common issue after staff turnover, server migrations, or Active Directory cleanups.

The Quick Fix: sp_changeobjectowner

The classic one-liner for this problem uses the undocumented system stored procedure sp_msforeachtable combined with sp_changeobjectowner:

EXEC sp_msforeachtable "sp_changeobjectowner '?', 'dbo'"

This iterates through every table in the current database and reassigns ownership to dbo. It's been in circulation for years and it works - but there are important caveats.

First, sp_changeobjectowner is deprecated. Microsoft marked it for removal in a future version of SQL Server, and it has been deprecated since SQL Server 2005. It still works in SQL Server 2019 and 2022 as of writing, but relying on deprecated procedures in production is a risk you should document and plan to address.

Second, sp_msforeachtable is undocumented. Microsoft does not guarantee its behaviour or continued availability. It also only covers tables - not views, stored procedures, functions, or other objects.

Third, this script changes ownership on all tables in the current database context. Make sure you're connected to the right database before running it.

The Modern Approach: ALTER AUTHORIZATION

For SQL Server 2005 and later, the correct way to change object ownership is with ALTER AUTHORIZATION. This is the documented, supported method that Microsoft recommends.

To change ownership on a single object:

ALTER AUTHORIZATION ON OBJECT::dbo.YourTableName TO dbo;

To handle all objects across the database - not just tables - use a script that queries the system catalog and generates the necessary statements:

SELECT
    'ALTER AUTHORIZATION ON OBJECT::['
    + SCHEMA_NAME(schema_id)
    + '].['
    + name
    + '] TO [dbo];' AS FixStatement
FROM sys.objects
WHERE
    type IN ('U', 'V', 'P', 'FN', 'IF', 'TF', 'TR')
    AND SCHEMA_NAME(schema_id) != 'sys'
    AND principal_id IS NOT NULL
    AND principal_id != (SELECT principal_id FROM sys.database_principals WHERE name = 'dbo');

This query returns ALTER AUTHORIZATION statements for any object that is not already owned by dbo. The type codes cover tables (U), views (V), stored procedures (P), scalar functions (FN), inline table-valued functions (IF), multi-statement table-valued functions (TF), and triggers (TR).

Review the output before executing. Copy the results and run them as a batch, or use dynamic SQL to execute them directly:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql +
    'ALTER AUTHORIZATION ON OBJECT::['
    + SCHEMA_NAME(schema_id)
    + '].['
    + name
    + '] TO [dbo];' + CHAR(13)
FROM sys.objects
WHERE
    type IN ('U', 'V', 'P', 'FN', 'IF', 'TF', 'TR')
    AND SCHEMA_NAME(schema_id) != 'sys'
    AND principal_id IS NOT NULL
    AND principal_id != (SELECT principal_id FROM sys.database_principals WHERE name = 'dbo');

PRINT @sql;
-- Review the output, then uncomment the next line to execute
-- EXEC sp_executesql @sql;

The PRINT statement lets you review exactly what will run before committing to it. Always do this in a non-production environment first.

What About Schemas?

It's worth distinguishing between object ownership and schema ownership. In SQL Server 2005 and later, schemas are separate from users. The dbo schema is owned by the dbo user, and objects in the dbo schema are not necessarily owned by dbo at the object level.

If you're also dealing with objects in non-dbo schemas, you may need to transfer those objects to the dbo schema as well. That's a separate operation using ALTER SCHEMA:

ALTER SCHEMA dbo TRANSFER OldSchema.ObjectName;

Changing the schema and changing the owner are two different things. Don't conflate them. In most migration scenarios, you'll need to address both.

Before You Run This in Production

A few checks to run before executing any ownership change script:

  1. Back up the database first. Ownership changes are not easily reversible without knowing the original owners.
  2. Check for application dependencies. Some applications reference objects using schema-qualified names. If you're also moving objects between schemas, connection strings and stored procedures may break.
  3. Run on a test restore first. Restore a copy of the database to a dev or test environment and validate the script output there before touching production.
  4. Check for orphaned users. If objects are owned by logins that no longer exist, you may also have orphaned database users. Run EXEC sp_change_users_login 'Report' to identify them.
  5. Document the original state. Before making changes, export the list of current object owners so you have a record.

How to Check Current Object Ownership

Before running any fix, it's useful to see exactly what you're dealing with. This query shows all objects with a non-dbo owner:

SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS ObjectName,
    type_desc AS ObjectType,
    USER_NAME(principal_id) AS Owner
FROM sys.objects
WHERE
    principal_id IS NOT NULL
    AND principal_id != (SELECT principal_id FROM sys.database_principals WHERE name = 'dbo')
ORDER BY ObjectType, SchemaName, ObjectName;

If this returns no rows, all your objects are already owned by dbo and no action is needed. If it returns a long list, you've found the source of your permission issues.

Key Takeaways

  • Object ownership mismatches break SQL Server permission chains and are a common cause of access denied errors after migrations or staff changes.
  • The legacy sp_changeobjectowner script works but is deprecated. Use ALTER AUTHORIZATION for any new or production work.
  • The sp_msforeachtable approach only covers tables. Use a sys.objects query to capture views, procedures, functions, and triggers as well.
  • Always review generated SQL before executing, and test on a non-production restore first.
  • Changing object ownership and changing object schema are separate operations - you may need to address both in a migration scenario.

If you're dealing with object ownership issues as part of a broader database migration or inherited environment cleanup, a structured assessment makes the process significantly less risky. DBA Services provides SQL Server health checks and managed DBA support across Australia, helping teams identify and resolve exactly these kinds of structural issues before they cause production incidents. Get in touch to find out how we can help.