Support Thread

Change Sql Object Ownership

Change Sql Object Ownership — APG vNext Guide

Change Sql Object Ownership — a configuration and setup topic from the APG vNext support community.

Key Configuration Points

  • IIS App Pool: .NET 4.0 Integrated Pipeline mode is required.
  • Connection String: Verify SQL Server name, database, and credentials in web.config.
  • File Permissions: App pool identity needs read/write on the upfiles folder.
  • URL Rewriting: Install IIS URL Rewrite 2.0 module for clean SEO-friendly URLs.
  • Full-Text Search: Enable SQL Server Full-Text Indexing for forum search.

More Help

Changing SQL Server Object Ownership for APG vNext

APG vNext database objects are created under the dbo schema by default. On shared hosting where the app user doesn't own dbo, all object references fail with "Invalid object name" errors. This guide covers transferring ownership and configuring the DBOwnerPrefix.

Diagnose the Issue

SELECT TABLE_SCHEMA, TABLE_NAME
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME LIKE 'apg_%'
ORDER  BY TABLE_SCHEMA;

Transfer All APG Tables to dbo

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME + ';'
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME LIKE 'apg_%' AND TABLE_SCHEMA != 'dbo';
EXEC sp_executesql @sql;

Alternative: Configure DBOwnerPrefix

<add key="APG.DBOwnerPrefix" value="apguser." />

Grant Permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [IIS_AppUser];
GRANT EXECUTE ON SCHEMA::dbo TO [IIS_AppUser];

Related Resources

Changing SQL Object Ownership in APG vNext

SQL Server objects (tables, stored procedures, views) in an APG vNext database are owned by the schema that contains them. APG vNext uses the dbo schema by default. If your database was set up with a different schema owner or if objects were created under a custom schema during a non-standard installation, you may need to transfer ownership back to dbo for APG vNext to function correctly.

Diagnosing Ownership Issues

-- Check schema ownership for APG vNext objects:
SELECT s.name AS SchemaName, u.name AS Owner,
       o.name AS ObjectName, o.type_desc AS ObjectType
FROM   sys.schemas s
JOIN   sys.database_principals u ON u.principal_id = s.principal_id
JOIN   sys.objects o ON o.schema_id = s.schema_id
WHERE  o.name LIKE 'apg_%'
ORDER  BY s.name, o.name;
-- Expected: all objects in schema "dbo", owned by "dbo"

Transferring Schema Ownership to dbo

-- Transfer all APG vNext objects to dbo schema:
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL += 'ALTER SCHEMA dbo TRANSFER [' + s.name + '].[' + o.name + '];' + CHAR(13)
FROM   sys.schemas s
JOIN   sys.objects o ON o.schema_id = s.schema_id
WHERE  s.name != 'dbo' AND o.name LIKE 'apg_%';
EXEC sp_executesql @SQL;
PRINT 'Transfer complete. Rows affected: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

Changing the Database Owner (dbo)

-- Change the database owner to the APG vNext service account:
USE [YourForumDB];
EXEC sp_changedbowner 'APGServiceAccount';
-- Verify:
SELECT SUSER_SNAME(owner_sid) AS DatabaseOwner
FROM   sys.databases
WHERE  name = DB_NAME();

Why Ownership Matters for Stored Procedures

APG vNext uses stored procedures extensively for complex queries (search, notification dispatching, ranking calculation). If a stored procedure is owned by a different user than the tables it queries, SQL Server's ownership chaining rules may deny access even if the application pool identity has EXECUTE permission on the procedure. This manifests as sporadic "Object does not exist or you do not have permission" errors that are difficult to diagnose without checking ownership chains. Consistent dbo ownership for all APG vNext objects eliminates this class of permission error entirely.

Preventing Ownership Issues After Installation

Always run the APG vNext installer or upgrade scripts while connected to SQL Server as a user with db_owner role on the target database. Scripts run as non-owner users create objects under the current user's default schema, leading to ownership fragmentation. Use the EXECUTE AS clause or connect as sa or a dedicated db_owner service account to ensure all objects are created under the dbo schema from the start.

Best Practices for SQL Server Schema Management

Maintaining consistent schema ownership for all APG vNext database objects prevents a wide class of intermittent permission errors that are difficult to debug without deep SQL Server knowledge. Beyond APG vNext, applying schema management best practices to your entire SQL Server environment reduces operational risk when deploying updates, adding new application pools, or migrating to new servers. Document your database schema ownership policies and include schema verification in your post-deployment checklist to catch ownership drift before it causes production issues. Running a weekly SQL Server maintenance job that audits object ownership and alerts on anomalies is a worthwhile addition to any production database monitoring setup.

Schema Issues After Applying SQL Server Updates

Occasionally, SQL Server cumulative updates or Service Packs can change internal schema handling behaviour, causing objects that were previously accessible to be reported as missing or inaccessible. If you encounter unexpected APG vNext errors immediately after a SQL Server update, check object ownership before investigating the application layer. SQL Server patch notes occasionally document schema-related behaviour changes in the "Fixed bugs" section. Always test APG vNext on a staging environment after applying SQL Server updates before rolling them to production.

Related Resources

Looking for more help? Browse the support forum or check the Knowledge Base.