SQL Server Agent作业中为新恢复数据库添加用户的正确方法

问题根源 #

你遇到的“login failed for user”错误，本质是刚恢复的第三方数据库中没有DOMAIN\svc.an的用户映射，而该用户仅拥有public和dbcreator服务器角色——默认情况下，dbcreator角色成员对恢复完成的数据库没有直接访问权限，因此执行USE [SPDB]时会触发权限验证失败，后续创建用户的语句无法执行。

正确解决方案 #

不需要授予sysadmin角色，以下两种方法均可解决：

方法1：修改恢复存储过程，内置用户创建逻辑

直接在RestoreSPDB存储过程末尾添加动态SQL，利用dbcreator角色对数据库的修改权限，在恢复完成后自动创建用户并授权：

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[RestoreSPDB]    Script Date: 8/29/2023 1:42:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RestoreSPDB]
    @DBName nvarchar(256), --DB name to restore
    @DBRestoreFilePath nvarchar(512)
AS
BEGIN
    SET NOCOUNT ON;

    ALTER DATABASE [SPDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE [SPDB] FROM  DISK = @DBRestoreFilePath WITH  FILE = 1,  MOVE N'Template_Data_Data' TO N'Path\to\DB.mdf',  MOVE N'Template_Data_Log' TO N'Path\to\DB.ldf',  NOUNLOAD,  REPLACE,  STATS = 5

    ALTER DATABASE [SPDB] SET MULTI_USER    

    -- 新增：动态创建用户并授权
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'USE [SPDB];
    CREATE USER [DOMAIN\svc.an] FOR LOGIN [DOMAIN\svc.an];
    ALTER ROLE db_owner ADD MEMBER [DOMAIN\svc.an];'
    EXEC sp_executesql @SQL
END
GO

方法2：修改后续执行脚本，通过master上下文执行跨库操作

如果不想修改原有存储过程，将原本的后续脚本替换为动态SQL，从master库上下文执行SPDB的用户创建操作（利用dbcreator角色的数据库修改权限）：

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'USE [SPDB];
CREATE USER [DOMAIN\svc.an] FOR LOGIN [DOMAIN\svc.an];
ALTER ROLE db_owner ADD MEMBER [DOMAIN\svc.an];'
EXEC sp_executesql @SQL

原理说明 #

dbcreator角色成员拥有创建、修改、恢复数据库的权限，通过master库执行动态SQL操作目标数据库时，权限验证基于服务器角色而非目标数据库的用户映射，因此可以绕过“无法访问SPDB”的问题，成功完成用户创建与授权。

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