A linked server can quietly turn an ordinary local login into a remote administrator. Reviewing linked server logins starts with the mapping that applies when no local login is named.
List Every Mapping for Linked Server Logins
sys.linked_logins joins to sys.servers to show each linked server's local principal, whether it uses the current security context, and any remote login name. A local_principal_id of 0 represents a mapping for all local logins, and its local_login column comes back NULL. I start there because a "for all logins" mapping to a remote sysadmin account can grant more reach than any local database role suggests. Review the remote principal's actual rights on the destination too. A remote name alone does not prove its role membership.
SELECT s.name AS linked_server, ll.local_principal_id,
sp.name AS local_login, ll.uses_self_credential,
ll.remote_name
FROM sys.linked_logins AS ll
JOIN sys.servers AS s ON s.server_id = ll.server_id
LEFT JOIN sys.server_principals AS sp
ON sp.principal_id = ll.local_principal_id
ORDER BY s.name, sp.name;Understand the Default Path
SQL Server linked servers get a default mapping at creation. In my test, sp_addlinkedserver added an all-logins mapping that uses the caller's own credentials. Test what it actually does rather than assuming the wizard choices were harmless. I inspect the linked server definition, security settings, remote login roles, and application dependencies. A blanket fixed remote credential is especially risky because a low-privilege local user can inherit the remote account's power. Remove or replace it only after finding legitimate callers. A sudden permission failure in a production report is not the ideal way to discover a dependency.
Document which local identities need which remote databases and operations. The answer is rarely "everyone needs sysadmin."
Test Linked Server Logins as a Low-Privilege User
In a controlled environment, use a representative low-privilege local login and run a harmless remote identity query. Capture the returned remote SYSTEM_USER and sysadmin membership. The block below takes a shortcut with EXECUTE AS from master, and EXEC AT needs RPC OUT enabled on the link. Use REVERT even when the test fails. For self-credential mappings, Kerberos delegation and double-hop configuration affect whether the test succeeds. I do not call a failed connection secure by default; it can be a broken auth path rather than a good permission design.
My own test showed why. On SQL Server 2025, the impersonated call stopped with error 7416 even with an explicit mapping for the login. Yet after I mapped all logins to a low-privilege remote account, a direct call from my own session ran as that account. Treat an error from the shortcut as unproven, and repeat the query from a real session as that login.
USE master;
EXECUTE AS LOGIN = N'AppReader';
BEGIN TRY
EXEC (N'SELECT SYSTEM_USER AS remote_login,
IS_SRVROLEMEMBER(N''sysadmin'') AS remote_sysadmin') AT [ReportingLink];
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS test_error;
END CATCH;
REVERT;Replace Blanket Rights
Create specific mappings for approved local logins to remote accounts with only the rights they need, or use self-credential mapping with correctly configured Kerberos delegation. Remove the broad default mapping through the documented linked-server login procedure once dependencies are identified. I test each application route before and after. A remote account used for read-only reporting should not be a sysadmin because a setup screen made it easy.
Use separate remote accounts for distinct trust levels. A shared credential across several linked servers makes audit and rotation harder. Keep credentials in approved secret management, not a script pasted into a ticket.
Check the Remote Boundary
Linked queries can reach more than the intended table if the remote login has broad database or server rights. Test SELECT, metadata visibility, and a harmless denied operation from the low-privilege identity. Verify remote audit records show the expected principal. I also review RPC and RPC OUT settings, since enabling remote procedure calls changes what the link can do. Cross-server ownership and delegation can be subtle; the test must use the actual application identity and host where possible.
What happens when the remote credential rotates? A narrow mapping with a documented owner can be updated deliberately. A forgotten blanket mapping tends to fail at the least convenient time.
Inspect Every Local Principal Path
A linked-server mapping can apply to one login, all local logins, or the current security context. Join sys.linked_logins to sys.server_principals and review rows with local_principal_id 0 separately. Include Windows groups and service identities in the inventory. I compare the remote_name with the actual remote login's roles and database grants. A harmless-looking name can still map to a remote sysadmin. Conversely, uses_self_credential can rely on Kerberos delegation and fail from an application server even when it works in a DBA session.
Do not grant a broad remote role just to fix that failure. Diagnose SPNs, delegation, and the intended identity chain first. A connectivity repair that changes every caller to one powerful remote account turns a configuration problem into a security problem.
Stage the Replacement Mapping
Create a low-privilege remote principal with the exact database rights needed, then add a specific local mapping for one test identity. Run the approved remote queries through the linked server and inspect remote audit logs. Next, test a denied action to confirm the boundary. Move callers in a controlled sequence. Only after they succeed should you remove the blanket mapping. I keep a rollback path that restores the previous application function without restoring remote sysadmin access; that can require a temporary scoped grant agreed in advance.
What if an old report uses the link once a quarter? Search job steps, procedure definitions, and connection logs before deletion, and ask the owner. A missing call during one week is not proof the link is unused. I document the mapping change and monitor remote login failures after release. The best result is not just fewer high-privilege mappings; it is a tested path for every legitimate caller.
Review remote login rights directly on the destination. A mapping to a named account does not imply that account is low privilege. I query its role membership and database grants under approved access, then test the exact linked operation from the source. Both sides of the trust boundary must agree on the least-privilege design.
Keep an Inventory of Linked Server Logins
Record each linked server, its purpose, local mappings, remote principal, rights, owner, and last test date. Review the all-logins mappings on a schedule. I compare the inventory with sys.linked_logins after migrations because old links tend to outlive the reports that needed them. Remove unused links through change control once callers are confirmed absent.
The final check is practical: a low-privilege user can run the approved query and cannot become a remote administrator. That is a stronger security statement than "the linked server works."
Related reading on this blog: Linked Servers and What Goes Wrong With Them and FIX: Linked Server Error 7416: Access to the remote server is denied because no login-mapping exists.
A linked server is not a shortcut around permissions, it is a trust boundary to manage.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
