Post-Restore Checklist: Owner, Trustworthy, Broker and Users

A post-restore checklist covers what a successful RESTORE does not carry over: the owner, TRUSTWORTHY, Service Broker and the users. The restore says it worked. The application then fails on its first call. Check these four things before you let anyone back in.

The restore that worked and the app that did not

Picture a Monday move. You restore the production database onto a new server. The messages say 100 percent, no errors. You tell the team it is ready.

Then the application cannot log in, a messaging queue sits silent, and a report that needs TRUSTWORTHY now fails. The data came across fine. The settings around the data did not.

I will build a small copy of that situation and run the move myself. The demo creates two server-level logins, DemoOwner and DemoApp, plus a database called SqlAuthorityDemo. It removes all of it at the end. The passwords are throwaway values for this demo only.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
IF SUSER_ID(N'DemoOwner') IS NOT NULL DROP LOGIN DemoOwner;
IF SUSER_ID(N'DemoApp') IS NOT NULL DROP LOGIN DemoApp;
GO
CREATE LOGIN DemoOwner WITH PASSWORD = 'Demo#Owner-2026-x', CHECK_POLICY = OFF;
CREATE LOGIN DemoApp WITH PASSWORD = 'Demo#App-2026-x', CHECK_POLICY = OFF;
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER AUTHORIZATION ON DATABASE::SqlAuthorityDemo TO DemoOwner;
ALTER DATABASE SqlAuthorityDemo SET TRUSTWORTHY ON;
GO
USE SqlAuthorityDemo;
CREATE USER AppUser FOR LOGIN DemoApp;

Start with an inventory

Run this in the database before and after any restore. The first query reads the owner, compatibility level, TRUSTWORTHY and Broker. The second finds users that have no matching login, which are called orphans. It compares the SIDs, because names can lie.

SELECT name, SUSER_SNAME(owner_sid) AS owner_name, compatibility_level,
       is_trustworthy_on, is_broker_enabled
FROM sys.databases
WHERE database_id = DB_ID();

SELECT u.name AS orphan_user
FROM sys.database_principals AS u
LEFT JOIN sys.server_principals AS l ON l.sid = u.sid
WHERE u.authentication_type_desc = 'INSTANCE'
  AND u.type IN ('S', 'U', 'G') AND u.principal_id > 4
  AND l.sid IS NULL;

Before the move, the owner is DemoOwner, TRUSTWORTHY is 1, Broker is 1, and the orphan list is empty. That is the healthy state.

Back up, lose a login, restore

Now play the move. Back up the database, drop it, and drop DemoApp too. A new server would not have that login either. Then restore.

This writes a backup file to C:\Temp. SQL Server must be able to write there. The first line creates the folder if it is missing. It and the cleanup helper at the end are undocumented procedures, so use them in demos only. I gave the file a made-up extension, so the cleanup at the end can only touch this one file.

USE master;
EXEC master.sys.xp_create_subdir N'C:\Temp';

BACKUP DATABASE SqlAuthorityDemo TO DISK = N'C:\Temp\SqlAuthorityDemo.pbak' WITH INIT;

DROP DATABASE SqlAuthorityDemo;
DROP LOGIN DemoApp;

RESTORE DATABASE SqlAuthorityDemo FROM DISK = N'C:\Temp\SqlAuthorityDemo.pbak';

Read the inventory again

Same two queries, new answers. The owner is now whoever ran the restore. TRUSTWORTHY is back to 0. Broker is 0, so messaging is off. AppUser shows up as an orphan, because its login did not travel with the database.

USE SqlAuthorityDemo;

SELECT name, SUSER_SNAME(owner_sid) AS owner_name, compatibility_level,
       is_trustworthy_on, is_broker_enabled
FROM sys.databases
WHERE database_id = DB_ID();

SELECT u.name AS orphan_user
FROM sys.database_principals AS u
LEFT JOIN sys.server_principals AS l ON l.sid = u.sid
WHERE u.authentication_type_desc = 'INSTANCE'
  AND u.type IN ('S', 'U', 'G') AND u.principal_id > 4
  AND l.sid IS NULL;

Fix each finding on purpose

Fix them one at a time, and say why. Set the owner to a stable, approved login. Turn Broker back on with ENABLE_BROKER, which keeps the existing identity. NEW_BROKER would create a new identity and end existing conversations, so save it for copies, not for a real move.

I leave TRUSTWORTHY at 0 on purpose. Turn it on only if a reviewed design needs it. For the orphan, recreate the login and use ALTER USER WITH LOGIN, which keeps the user and its permissions.

USE master;
ALTER AUTHORIZATION ON DATABASE::SqlAuthorityDemo TO DemoOwner;
ALTER DATABASE SqlAuthorityDemo SET ENABLE_BROKER;
CREATE LOGIN DemoApp WITH PASSWORD = 'Demo#App-2026-x', CHECK_POLICY = OFF;
GO
USE SqlAuthorityDemo;
ALTER USER AppUser WITH LOGIN = DemoApp;

SELECT name, SUSER_SNAME(owner_sid) AS owner_name, is_trustworthy_on, is_broker_enabled
FROM sys.databases
WHERE database_id = DB_ID();

SELECT u.name AS orphan_user
FROM sys.database_principals AS u
LEFT JOIN sys.server_principals AS l ON l.sid = u.sid
WHERE u.authentication_type_desc = 'INSTANCE'
  AND u.type IN ('S', 'U', 'G') AND u.principal_id > 4
  AND l.sid IS NULL;

The owner is back, Broker is on, and the orphan list is empty. TRUSTWORTHY stays 0.

What lives outside the database

DemoApp is the lesson. A login is a server object, so it did not come along. Agent jobs, linked servers and credentials are the same. Check them separately, and test with the real application account, not your own admin login.

Last, clean up. This drops the database and both logins, then deletes the backup file.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
DROP LOGIN DemoApp;
DROP LOGIN DemoOwner;
EXEC master.sys.xp_delete_file 0, N'C:\Temp', N'pbak', N'2999-01-01';

Keep the application closed until the checklist comes back clean.

A finished restore is not a finished move, it is where the checklist starts.

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