How to Prohibit Specific Accounts from Using LinkServer? Block Directly with a Fake Account

Conclusion#

Overriding LinkServer logins with “fake account mapping” to ensure specified users always fail to connect is the simplest and most controllable blocking method.

Where It’s Suitable#

To restrict “specific accounts” from using LinkServer
To avoid affecting other users (maintaining public availability)
When precise control of LinkServer with DENY is not possible
When permissions are already chaotic and quick remediation is needed

Steps#

Use sp_helplinkedsrvlogin to view mappings
You can see who maps to which remote account
Determine if there is NULL (meaning shared by everyone)

1
EXEC sp_helplinkedsrvlogin;

2. Remove Existing Mappings for the Target User#

For all LinkServers
Delete the login mapping for that User
To prevent interference from old settings

1
EXEC sp_droplinkedsrvlogin
2
    @rmtsrvname = N'LinkServerName',
3
    @locallogin = N'UserA';

Core method: Specify a non-existent account
When the User calls LinkServer → login will definitely fail
Equivalent to a “soft DENY”

1
EXEC sp_addlinkedsrvlogin
2
    @rmtsrvname = N'LinkServerName',
3
    @useself = N'False',
4
    @locallogin = N'UserA',
5
    @rmtuser = N'FakeDenyUser',
6
    @rmtpassword = N'WrongPassword';

Additional Notes#

There is no DENY USE LINKSERVER permission; control can only be done via mapping
@locallogin = NULL means shared by everyone; special attention is needed for the override order
This method does not affect other users (only targets specific accounts)

Command / Example Summary#

Click to expand

1
-- 檢視權限
2
EXEC sp_helplinkedsrvlogin;
3

4
-- 動態產生：刪除 + 建立 Fake Mapping
5
DECLARE @UserName sysname = N'UserA';
6
DECLARE @FakeUser sysname = N'FakeDenyUser';
7
DECLARE @FakePassword nvarchar(200) = N'WrongPassword';
8

9
DECLARE @SQL nvarchar(max) = N'';
10

11
-- 1. 刪除 mapping
12
SELECT
13
    @SQL = @SQL +
14
    N'BEGIN TRY
15
        EXEC sp_droplinkedsrvlogin
16
            @rmtsrvname = N''' + REPLACE(name, '''', '''''') + ''',
17
            @locallogin = N''' + REPLACE(@UserName, '''', '''''') + ''';
18
    END TRY
19
    BEGIN CATCH
20
    END CATCH;' + CHAR(13) + CHAR(10)
21
FROM sys.servers
22
WHERE is_linked = 1;
23

24
-- 2. 建立 Fake mapping
25
SELECT
26
    @SQL = @SQL +
27
    N'EXEC sp_addlinkedsrvlogin
28
        @rmtsrvname = N''' + REPLACE(name, '''', '''''') + ''',
29
        @useself = N''False'',
30
        @locallogin = N''' + REPLACE(@UserName, '''', '''''') + ''',
31
        @rmtuser = N''' + REPLACE(@FakeUser, '''', '''''') + ''',
32
        @rmtpassword = N''' + REPLACE(@FakePassword, '''', '''''') + ''';'
33
    + CHAR(13) + CHAR(10)
34
FROM sys.servers
35
WHERE is_linked = 1;
36

37
-- Debug
38
PRINT @SQL;
39

40
-- 執行
41
EXEC sp_executesql @SQL;

Conclusion#

No permission control for LinkServer? Then use the ‘make them unable to connect forever’ trick, it’s clean and decisive. Practical advice: Design permissions with separate accounts from the start, otherwise you’ll only be patching holes later.

Conclusion#

Where It’s Suitable#

Steps#

1. Confirm Current LinkServer Login Settings#

2. Remove Existing Mappings for the Target User#

3. Create a “Fake Account” to Force Login Failure#

Additional Notes#

Command / Example Summary#

Conclusion#