针对从其他服务器备份文件恢复数据库后，需要处理用户映射、权限配置和旧用户清理的场景，我写了这个SQL Server存储过程，能把一系列手动操作自动化，具体功能和完整代码如下：

核心功能清单 #

• 动态构建磁盘备份恢复语句，自动适配备份文件路径与目标数据库文件位置
• 在恢复后的数据库中创建新用户（解决跨服务器后SID不兼容导致的用户映射失效问题）
• 为新用户授予两个指定架构的读写权限
• 将新用户分配至两个独立的数据库角色
• 清理原服务器旧数据库中的废弃旧用户

完整存储过程代码 #

CREATE PROCEDURE dbo.RestoreDBAndManageUsers
    @BackupFilePath NVARCHAR(500),
    @TargetDBName NVARCHAR(128),
    @NewUserName NVARCHAR(128),
    @NewUserLogin NVARCHAR(128),
    @Schema1 NVARCHAR(128),
    @Schema2 NVARCHAR(128),
    @Role1 NVARCHAR(128),
    @Role2 NVARCHAR(128),
    @OldDBNameOnSource NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 动态生成数据库恢复语句（可根据实际存储路径调整MOVE子句）
    DECLARE @RestoreSQL NVARCHAR(MAX);
    SET @RestoreSQL = N'RESTORE DATABASE [' + @TargetDBName + N']
    FROM DISK = ''' + @BackupFilePath + N'''
    WITH REPLACE,
         MOVE ''' + @TargetDBName + N''' TO ''D:\SQLData\' + @TargetDBName + N'.mdf'',
         MOVE ''' + @TargetDBName + N'_log'' TO ''E:\SQLLogs\' + @TargetDBName + N'_log.ldf''';
    
    EXEC sp_executesql @RestoreSQL;

    -- 2. 在目标数据库创建新用户并关联登录账号
    DECLARE @CreateUserSQL NVARCHAR(MAX);
    SET @CreateUserSQL = N'USE [' + @TargetDBName + N'];
    CREATE USER [' + @NewUserName + N'] FOR LOGIN [' + @NewUserLogin + N'];';
    
    EXEC sp_executesql @CreateUserSQL;

    -- 3. 为用户授予两个指定架构的核心操作权限
    DECLARE @GrantSchemaSQL NVARCHAR(MAX);
    SET @GrantSchemaSQL = N'USE [' + @TargetDBName + N'];
    GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[' + @Schema1 + N'] TO [' + @NewUserName + N'];
    GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[' + @Schema2 + N'] TO [' + @NewUserName + N'];';
    
    EXEC sp_executesql @GrantSchemaSQL;

    -- 4. 将用户添加到指定的两个数据库角色
    DECLARE @AddToRoleSQL NVARCHAR(MAX);
    SET @AddToRoleSQL = N'USE [' + @TargetDBName + N'];
    EXEC sp_addrolemember ''' + @Role1 + N''', ''' + @NewUserName + N''';
    EXEC sp_addrolemember ''' + @Role2 + N''', ''' + @NewUserName + N''';';
    
    EXEC sp_executesql @AddToRoleSQL;

    -- 5. 清理原服务器旧数据库中的同名旧用户（避免残留权限）
    DECLARE @DeleteOldUserSQL NVARCHAR(MAX);
    SET @DeleteOldUserSQL = N'USE [' + @OldDBNameOnSource + N'];
    IF EXISTS (SELECT 1 FROM sysusers WHERE name = ''' + @NewUserName + N''')
    BEGIN
        DROP USER [' + @NewUserName + N'];
    END;';
    
    EXEC sp_executesql @DeleteOldUserSQL;

    PRINT '数据库恢复、用户创建与权限配置全流程完成！';
END
GO

调用示例 #

执行时只需传入对应参数即可，示例如下：

EXEC dbo.RestoreDBAndManageUsers
    @BackupFilePath = '\\RemoteBackupServer\DBBackups\OldProductionDB.bak',
    @TargetDBName = 'NewRestoredDB',
    @NewUserName = 'AppServiceUser',
    @NewUserLogin = 'CORP\AppServiceLogin',
    @Schema1 = 'Sales',
    @Schema2 = 'Inventory',
    @Role1 = 'db_datareader',
    @Role2 = 'db_datawriter',
    @OldDBNameOnSource = 'OldProductionDB';

注意事项：

• 执行此存储过程需要具备ALTER ANY DATABASE、CREATE USER权限，以及目标数据库的相应操作权限
• 确保SQL Server服务账户有权访问远程备份文件路径
• 可根据实际存储路径调整恢复语句中的MOVE子句，匹配目标服务器的数据库文件存储位置

内容的提问来源于stack exchange，提问作者Charles Bretana