如何撤销sp_addlinkedsrvlogin操作并恢复SQL Server权限？

1. 用高权限账号登录本地SQL实例 #

直接使用sa账号或其他拥有sysadmin服务器角色的账号，登录到出现权限问题的本地SQL Server实例——当前用户权限不足，必须通过高权限账号执行后续操作。

2. 删除错误添加的链接服务器登录映射 #

执行以下T-SQL命令，撤销之前错误创建的链接服务器登录映射：

EXEC sp_droplinkedsrvlogin 
    @rmtsrvname = 'servername', 
    @locallogin = 'null'

注意：这里的@locallogin='null'要和你之前执行sp_addlinkedsrvlogin时的参数完全一致，因为你当时传入的是字符串'null'而非NULL值。

3. 确认并恢复当前用户的sysadmin权限 #

执行以下查询，检查当前用户是否仍在sysadmin服务器角色中：

SELECT sp.name AS LoginName, sr.name AS ServerRole
FROM sys.server_role_members srm
JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
WHERE sp.name = '你的当前用户名' AND sr.name = 'sysadmin'

如果查询结果为空，说明当前用户的sysadmin角色被意外移除，执行以下命令添加回去：

EXEC sp_addsrvrolemember 
    @loginame = '你的当前用户名', 
    @rolename = 'sysadmin'

4. 重新配置正确的链接服务器登录映射（可选） #

根据你的需求重新设置链接服务器的登录规则：

• 让所有本地登录用户使用自身身份登录远程服务器：

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'servername', 
    @useself = 'true'

• 为特定本地用户设置映射：

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'servername', 
    @useself = 'false',
    @locallogin = '特定本地用户名',
    @rmtuser = '远程服务器用户名',
    @rmtpassword = '远程服务器密码'

5. 解决SQL Server Agent作业执行失败的问题 #

之前作业失败大概率是因为Agent服务账号没有访问远程服务器的权限，可按以下方式处理：

• 查看SQL Server Agent的服务账号（在系统服务管理器中找到SQL Server Agent服务，查看其登录身份）
• 在远程服务器上为该账号创建登录，并授予操作所需的权限
• 或者配置链接服务器的登录映射，将Agent服务账号映射到远程服务器的合法登录

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