Delegated database management

Delegation is useful when responsibility for an application or a specific database is shared with someone who is not a DBA. YourSqlDba allows an application owner or senior support user to perform limited operations without receiving unrestricted SQL Server privileges.

Two contexts are common:

• creating an archive, test, or validation copy from an existing database;
• managing a database upgrade tied to an application upgrade.

To create a test copy that is as current as possible, Maint.DuplicateDbFromBackupHistory is often the simplest and most resource-efficient method. It restores a database under another name from the source database’s backup history. By default, it adds a final transaction log backup, usually quick, so the copy is as current as possible without creating another full backup.

More specialized procedures can also create a copy-only backup, restore a specific file, or clean up backups associated with the delegated workflow. In all cases, YourSqlDba limits the delegated login to explicitly authorized source databases and, for restores, to target names derived from those sources.

Application upgrades present an additional problem. The upgrade may require exclusive access to the database and a reliable point to which it can be restored if the upgrade fails. Disconnecting the current sessions is not always enough: application services, connection pools, monitoring tools, or scheduled processes may immediately reconnect.

The maintenance-mode workflow groups the required steps: disconnect users, temporarily rename the database with the _MaintenanceMode suffix, establish a recovery point, then return to the normal name after validation. Clients configured with the original database name cannot reconnect during the operation. If the upgrade fails, the database can be restored to its initial state; if it succeeds, it is simply returned to normal use.

Non-sysadmin users must be registered with their access limits in the YourSqlDba.Maint.DelegatedDbManagement table. See the Authorization model to understand how to configure this, and Configure a delegated login for examples. After validating the delegated workflows, remove broader permissions that are no longer required, such as membership in the dbcreator fixed server role or the db_backupoperator fixed database role.

See the procedures for delegated backups, duplication and restore, backup cleanup, and the application-upgrade workflow.

Breaking change: Existing non-sysadmin scripts that call delegated procedures may stop working after upgrading. A sysadmin must add each login and its authorized source databases to Maint.DelegatedDbManagement before those scripts are run again. Restore targets must also follow the naming rules described below.

Authorization model

Delegation is configured in YourSqlDba.Maint.DelegatedDbManagement. The table contains one row per delegated login.

Column	Purpose
LoginName	Login returned by ORIGINAL_LOGIN() for the delegated user.
SourceDatabaseList	Comma-separated source databases authorized for general delegated operations.
MaintenanceModeDatabaseList	Optional comma-separated databases additionally authorized for the application-upgrade workflow.
CreatedAt	Date and time at which the row was created.
CreatedBy	Login that created the row.

SourceDatabaseList authorizes the backup, duplication, restore, and cleanup operations described on this page. MaintenanceModeDatabaseList authorizes the more specific maintenance-mode workflow. A database listed in SourceDatabaseList is already authorized for both categories; the second list is useful when a login should receive only the maintenance-mode authorization for a database.

Sysadmin logins are not restricted by this table.

Configure a delegated login

Run these statements as a sysadmin in the YourSqlDba database. Database names in both lists are separated by commas. You can edit the table directly in SSMS by selecting Edit Top 200 Rows from its context menu, or adapt the INSERT, UPDATE, and DELETE examples below.

Add a login

USE YourSqlDba;
GO

INSERT Maint.DelegatedDbManagement
       (LoginName, SourceDatabaseList, MaintenanceModeDatabaseList)
VALUES (N'DOMAIN\AppSupport',
        N'Payroll,Accounting',
        N'Payroll');

Change its authorization

The update replaces the complete contents of each list.

UPDATE Maint.DelegatedDbManagement
SET SourceDatabaseList = N'Payroll,Accounting,Reporting',
    MaintenanceModeDatabaseList = N'Payroll,Accounting'
WHERE LoginName = N'DOMAIN\AppSupport';

Review the configuration

SELECT LoginName,
       SourceDatabaseList,
       MaintenanceModeDatabaseList,
       CreatedAt,
       CreatedBy
FROM Maint.DelegatedDbManagement
ORDER BY LoginName;

Revoke delegation

DELETE Maint.DelegatedDbManagement
WHERE LoginName = N'DOMAIN\AppSupport';

Restore target naming rules

A delegated non-sysadmin login cannot restore over the source database or use an unrelated target name. The target must begin with the complete source name, followed by an underscore and a suffix.

For a source database named Payroll, valid targets include:

• Payroll_AppSupport
• Payroll_UpgradeTest
• Payroll_2026Q3

Invalid targets include:

• Payroll, because a delegated user cannot overwrite the source;
• PayrollTest, because the required underscore is missing;
• ProductionPayroll, because it is not derived from the authorized source.

Do not create a production database whose name follows the delegated naming pattern of another database. For example, if Payroll is delegated, a database such as Payroll_Production would look like an authorized derivative.

Delegated backup procedures

The following procedures require the source database to be present in SourceDatabaseList:

• Maint.SaveDbOnNewFileSet creates a backup using YourSqlDba naming and backup rules.
• Maint.SaveDbCopyOnly creates a copy-only backup at the specified path and file name.

Example:

EXEC Maint.SaveDbCopyOnly
    @DbName = N'Payroll',
    @PathAndFilename = N'D:\SQLBackups\Payroll_AppSupport.bak';

Delegated duplication and restore procedures

The following procedures enforce both the source authorization and target naming rules:

• Maint.DuplicateDb creates an intermediate backup, restores it under the target name, and deletes that intermediate backup by default.
• Maint.DuplicateDbFromBackupHistory restores from backups already recorded by YourSqlDba and can add a final transaction log backup before the restore.
• Maint.RestoreDb restores a specified backup file; the source database is validated from the backup information.

Maint.DuplicateDbFromBackupHistory is usually faster when the backup chain already exists: it avoids creating another full backup and reuses the subsequent transaction log backups already produced. The final transaction log backup it adds is kept in the existing log backup file; it then remains subject to the normal backup retention rules.

Examples:

EXEC Maint.DuplicateDbFromBackupHistory
    @SourceDb = N'Payroll',
    @TargetDb = N'Payroll_UpgradeTest';

EXEC Maint.DuplicateDb
    @SourceDb = N'Payroll',
    @TargetDb = N'Payroll_AppSupport';

Before restoring over an existing delegated target, YourSqlDba terminates its active sessions because a non-sysadmin user normally cannot do so. This is not done automatically for sysadmins: they may restore unrelated databases and must therefore handle active sessions explicitly when appropriate. The YourSqlDba procedure S#.KillDbUsers makes this task easier. Reminder: a SQL script cannot request confirmation, so verify these carefully before running them.

Delegated backup cleanup

Maint.DeleteOldBackups allows a delegated login to remove old backup files only for database variants derived from its authorized source databases. The same source-name, underscore, and suffix rule applies.

Review @Path, retention, extension, @IncDb, and @ExcDb carefully before running cleanup. A sysadmin is not restricted to delegated database variants and so you must be careful to ensure the filters are as expected.

Application-upgrade workflow

The maintenance-mode workflow introduced above provides the recovery and database-name transitions needed to control an application upgrade.

The workflow provides the following procedures. The restore step is optional and is used only when the upgrade must be rolled back.

1. Maint.PrepDbForMaintenanceMode disconnects users, renames the database with the _MaintenanceMode suffix, and establishes the recovery point.
2. Maint.RestoreDbAtStartOfMaintenanceMode restores the database to that recovery point (erasing the effects of a failed upgrade) while leaving the database under its maintenance-mode name.
3. Maint.ReturnDbToNormalUseFromMaintenanceMode returns the upgraded or restored database to its original name and normal use.

Example:

EXEC Maint.PrepDbForMaintenanceMode
    @DbList = N'Payroll';

-- Run and validate the application upgrade against Payroll_MaintenanceMode.

-- If the upgrade fails, return to the recovery point.
-- You can retry the upgrade without rerunning Maint.PrepDbForMaintenanceMode;
-- after each failure, restore that same recovery point again.
EXEC Maint.RestoreDbAtStartOfMaintenanceMode
    @DbList = N'Payroll';

-- Return either the upgraded database or the restored recovery point to service:
EXEC Maint.ReturnDbToNormalUseFromMaintenanceMode
    @DbList = N'Payroll';

Authorization for this workflow can come from either SourceDatabaseList or MaintenanceModeDatabaseList.

Upgrade checklist

Before upgrading an instance that already uses non-sysadmin management scripts:

1. Identify every login that calls one of the procedures listed on this page.
2. Record the source databases required by each login.
3. Insert or update the corresponding row in Maint.DelegatedDbManagement.
4. Update restore target names to use the SourceDatabase_suffix pattern.
5. Test each delegated workflow with the actual non-sysadmin login.
6. Review any production database names that could be confused with a delegated derivative.