The Problem

It's Monday morning. Your database administrator comes to you with some news: "We're moving Bob from the Sales team, and he's retiring. Oh, by the way, he owns the schema for our entire Order Management system."

Now  Bob's user account needs to be removed from the database, but you can't just delete him because he owns the Order schema. You decide to transfer schema ownership to someone else - seems simple enough, right? You run a quick command:

ALTER AUTHORIZATION ON SCHEMA::[Order] TO [User4];

The command completes instantly, and everything seems fine. Then the next day, you wake up to thousands of error messages coming into your application: permission denied. The users who had SELECT privileges can no longer read the data.

What just happened?

SQL Server silently removed every granular permission granted on the objects within that schema when you changed the owner of a schema. All your users ended up losing their access. Nobody warned you. There was no confirmation prompt from the system.

Let's look at the exact scenario using a simple example, then see three ways to fix it.

The Setup

Imagine you have an order management database with:

• User1: The original schema owner
• User2: A manager who needs SELECT access to the Sale table
• User3: A data analyst who needs UPDATE access to the SaleDate column only
• User4: The New schema owner.

Here's some code to create this scenario.

-- Create the database and schema
CREATE DATABASE OrderManagement;
GO

USE OrderManagement;
GO
-- Create logins
CREATE LOGIN [User1] WITH PASSWORD = 'P@ssw0rd123!';
CREATE LOGIN [User2] WITH PASSWORD = 'P@ssw0rd123!';
CREATE LOGIN [User3] WITH PASSWORD = 'P@ssw0rd123!';
CREATE LOGIN [User4] WITH PASSWORD = 'P@ssw0rd123!';


-- Create database users
CREATE USER [User1] FOR LOGIN [User1];
CREATE USER [User2] FOR LOGIN [User2];
CREATE USER [User3] FOR LOGIN [User3];
CREATE USER [User4] FOR LOGIN [User4];


-- User1 owns the schema
CREATE SCHEMA [Order] AUTHORIZATION [User1];

-- Create a sample table
CREATE TABLE [Order].[Sale]
(
    SaleID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    SaleDate DATE
);

INSERT INTO [Order].[Sale] VALUES (1, 'Wireless Bluetooth Speaker', '2024-01-15');

Now we grant specific permissions to each user:

-- User2 can SELECT the entire table
GRANT SELECT ON [Order].Sale TO [User2];

-- User3 can UPDATE only the SaleDate column
GRANT UPDATE ON [Order].Sale (SaleDate) TO [User3];

At this point, both users have exactly what they need.

Now we capture the permissions before they disappear with this script.

SELECT 
    dp.class AS PermissionClass,
    CASE 
        WHEN dp.class = 1 THEN 'OBJECT'
        WHEN dp.class = 3 THEN 'SCHEMA'
        WHEN dp.class = 4 THEN 'DATABASE'
    END AS SecurableType,
    CASE 
        WHEN dp.class = 3 THEN SCHEMA_NAME(dp.major_id)
        WHEN dp.class = 1 THEN OBJECT_SCHEMA_NAME(dp.major_id)
        ELSE NULL
    END AS SchemaName,
    CASE 
        WHEN dp.class = 1 THEN OBJECT_NAME(dp.major_id)
        ELSE NULL
    END AS ObjectName,
    c.name AS ColumnName,
    USER_NAME(dp.grantee_principal_id) AS GranteeUser,
    dp.permission_name AS PermissionName,
    -- The actual GRANT statement to reapply
    'GRANT ' + dp.permission_name +
    CASE 
        WHEN dp.class = 3 THEN ' ON SCHEMA::' + SCHEMA_NAME(dp.major_id)
        WHEN dp.class = 1 AND c.name IS NOT NULL THEN
            ' ON OBJECT::' + 
            OBJECT_SCHEMA_NAME(dp.major_id) + '.' +
            OBJECT_NAME(dp.major_id) +
            ' (' + c.name + ')'
        WHEN dp.class = 1 THEN
            ' ON OBJECT::' + 
            OBJECT_SCHEMA_NAME(dp.major_id) + '.' +
            OBJECT_NAME(dp.major_id)
        ELSE ''
    END
    + ' TO [' + USER_NAME(dp.grantee_principal_id) + '];' AS ReapplyStatement
FROM sys.database_permissions dp
LEFT JOIN sys.columns c
    ON dp.major_id = c.object_id
    AND dp.minor_id = c.column_id
WHERE dp.class IN (1, 3)  -- OBJECT and SCHEMA only
  AND dp.state = 'G'      -- GRANT only (not DENY)
  AND CASE 
        WHEN dp.class = 3 THEN SCHEMA_NAME(dp.major_id)
        WHEN dp.class = 1 THEN OBJECT_SCHEMA_NAME(dp.major_id)
        ELSE NULL
      END = 'Order'  -- Target schema
  AND USER_NAME(dp.grantee_principal_id) NOT IN ('dbo', 'sys', 'INFORMATION_SCHEMA', 'guest')
ORDER BY 
    USER_NAME(dp.grantee_principal_id),
    OBJECT_NAME(dp.major_id),
    c.name;

This query returns something like these results:

 Save these ReapplyStatement values.

The Moment of Truth: Change the Owner

Now let's move the schema to a new user.

ALTER AUTHORIZATION ON SCHEMA::[Order] TO [User4];

Now let's check if the permissions still exist. Result is empty result set and there are no permissions.

Solution : Capture and Reapply (The Quick Fix)

Once the permissions are gone, you can bring them back by executing the ReapplyStatements you saved earlier:

GRANT SELECT ON OBJECT::Order.Sale TO [User2];
GRANT UPDATE ON OBJECT::Order.Sale (SaleDate) TO [User3];

Verify they're back. Result is permissions restored.

Best Practices for Avoiding This Problem

First, use Database Roles. When you create a schema, immediately create the role structure to go with it. Here's how I would implement this in the example above.

CREATE SCHEMA [Order] AUTHORIZATION [dbo];  -- dbo as owner

CREATE ROLE [Order_Readers];
CREATE ROLE [Order_Writers];
CREATE ROLE [Order_Admins];

GRANT SELECT ON SCHEMA::[Order] TO [Order_Readers];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[Order] TO [Order_Writers];
GRANT EXECUTE ON SCHEMA::[Order] TO [Order_Admins];

ALTER ROLE [Order_Readers] ADD MEMBER [User1];
ALTER ROLE [Order_Writers] ADD MEMBER [User2];
ALTER ROLE [Order_Writers] ADD MEMBER [User3];
ALTER ROLE [Order_Admins] ADD MEMBER [User4];

Next, have schema owners be system accounts, not people. Use dbo or a dedicated service account as the schema owner instead of individual employees. This removes the need for ownership transfers when people leave.

Lastly, audit permissions regularly. Make the above query to get the permission part of your monthly or quarterly DBA checklist.

Conclusion

SQL Server's behavior of removing permissions when you change schema owners is well-documented but often forgotten. There are options:

1. If it already happened: Use the capture-and-reapply approach to restore permissions
2. Going forward: Switch to role-based permissions - it's the proper solution and saves you from this headache entirely

The cost of implementing roles properly is minimal compared to the cost of a permission-related outage or security incident.