如何先转移SQL Server用户的架构与作业所有权再删除用户？

以下是完整的步骤和代码，先转移deleteme用户拥有的架构和作业所有权，再执行删除操作，同时列出其他需要处理的依赖项以避免错误。

步骤1：转移所有数据库中deleteme拥有的架构所有权 #

将架构所有权转移至dbo（若需指定其他账户，替换代码中的dbo即可）：

EXEC master.sys.sp_MSforeachdb 'USE [?];
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''deleteme'')
BEGIN
    DECLARE @SchemaSql NVARCHAR(MAX) = '''';
    SELECT @SchemaSql += ''ALTER AUTHORIZATION ON SCHEMA::'' + QUOTENAME(s.name) + '' TO dbo;'' + CHAR(10)
    FROM sys.schemas s
    JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
    WHERE dp.name = ''deleteme'';
    
    IF @SchemaSql <> ''''
        EXEC sp_executesql @SchemaSql;
END
'

步骤2：转移SQL Agent作业的所有权 #

将deleteme拥有的作业所有权转移至sa（或指定的服务器级账户）：

DECLARE @JobSql NVARCHAR(MAX) = '''';
SELECT @JobSql += 'EXEC msdb.dbo.sp_update_job @job_id = '''' + CAST(job_id AS NVARCHAR(36)) + '''', @owner_login_name = ''sa'';' + CHAR(10)
FROM msdb.dbo.sysjobs j
JOIN master.sys.server_principals sp ON j.owner_sid = sp.sid
WHERE sp.name = 'deleteme';

IF @JobSql <> ''''
    EXEC sp_executesql @JobSql;

步骤3：删除所有数据库中的deleteme用户 #

优化原有代码，仅在存在用户时执行删除：

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
    DECLARE @Tsql NVARCHAR(MAX)
    SET @Tsql = ''''''

    SELECT @Tsql = ''DROP USER '' + QUOTENAME(d.name)
    FROM sys.database_principals d
    JOIN master.sys.server_principals s
        ON s.sid = d.sid
    WHERE s.name = ''deleteme''

    IF @Tsql <> ''''
        EXEC (@Tsql)
'

步骤4：删除服务器级登录deleteme #

IF EXISTS (SELECT 1 FROM master.sys.server_principals WHERE name = 'deleteme')
DROP LOGIN deleteme;

其他需处理的依赖项（避免删除失败） #

除架构和作业外，以下对象若由deleteme拥有也需处理：

1. 直接归属于用户的数据库对象 #

查询并转移所有权：

USE [目标数据库];
-- 查询对象
SELECT OBJECT_NAME(object_id) AS object_name, type_desc
FROM sys.objects
WHERE principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'deleteme');

-- 转移所有权示例
ALTER AUTHORIZATION ON OBJECT::[ObjectName] TO dbo;

2. 用户拥有的数据库角色 #

查询并转移所有权：

USE [目标数据库];
-- 查询角色
SELECT name AS role_name
FROM sys.database_principals
WHERE type = 'R' AND owner_principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'deleteme');

-- 转移所有权示例
ALTER AUTHORIZATION ON ROLE::[RoleName] TO dbo;

3. 证书/非对称密钥 #

查询并转移所有权：

USE [目标数据库];
-- 查询证书
SELECT name AS cert_name
FROM sys.certificates
WHERE principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'deleteme');

-- 查询非对称密钥
SELECT name AS key_name
FROM sys.asymmetric_keys
WHERE principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'deleteme');

-- 转移所有权示例
ALTER AUTHORIZATION ON CERTIFICATE::[CertName] TO dbo;
ALTER AUTHORIZATION ON ASYMMETRIC KEY::[KeyName] TO dbo;

4. 服务器级依赖项 #

• 登录关联的凭据：

  SELECT c.name AS credential_name
  FROM master.sys.credentials c
  JOIN master.sys.server_principals sp ON c.principal_id = sp.principal_id
  WHERE sp.name = 'deleteme';
  -- 移除关联或删除凭据
  ALTER CREDENTIAL [CredentialName] WITH IDENTITY = '原身份', SECRET = '原密钥';
  

• SQL Agent代理关联：

  SELECT p.name AS proxy_name
  FROM msdb.dbo.sysproxies p
  JOIN msdb.dbo.sysproxylogin pl ON p.proxy_id = pl.proxy_id
  JOIN master.sys.server_principals sp ON pl.sid = sp.sid
  WHERE sp.name = 'deleteme';
  -- 移除关联
  EXEC msdb.dbo.sp_revoke_login_from_proxy @login_name = 'deleteme', @proxy_name = 'ProxyName';
  

• 端点所有权：

  SELECT name AS endpoint_name
  FROM master.sys.endpoints
  WHERE principal_id = (SELECT principal_id FROM master.sys.server_principals WHERE name = 'deleteme');
  -- 转移所有权
  ALTER AUTHORIZATION ON ENDPOINT::[EndpointName] TO sa;
  

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