sp_droplinkedsrvlogin (T-SQL)

Removes an existing mapping between a login on the local server running Microsoft® SQL Server™ and a login on the linked server.

Syntax

sp_droplinkedsrvlogin [@rmtsrvname =] 'rmtsrvname',
    
[@locallogin =] 'locallogin'

Arguments
[@rmtsrvname =] 'rmtsrvname'
Is the name of a linked server that the SQL Server login mapping applies to. rmtsrvname is sysname, with no default. rmtsrvname must already exist.
[@locallogin =] 'locallogin'
Is the SQL Server login on the local server that has a mapping to the linked server rmtsrvname. locallogin is sysname, with no default. A mapping for locallogin to rmtsrvname must already exist. If NULL, the default mapping created by sp_addlinkedserver, which maps all logins on the local server to logins on the linked server, is deleted.
Return Code Values

0 (success) or 1 (failure)

Remarks

When the existing mapping for a login is deleted, the local server uses the default mapping created by sp_addlinkedserver when connecting to the linked server on behalf of that login. To change the default mapping, use sp_addlinkedsrvlogin.

If the default mapping is also deleted, only logins that have been explicitly given a login mapping to the linked server, using sp_addlinkedsrvlogin, can access the linked server.

sp_droplinkedsrvlogin cannot be executed from within a user-defined transaction.

Permissions

Only members of the sysadmin or securityadmin fixed server roles can execute sp_droplinkedsrvlogin.

Examples
A. Remove the login mapping for an existing user

This example removes the mapping for the login Mary from the local server to the linked server Accounts; as a result, login Mary uses the default login mapping.

EXEC sp_droplinkedsrvlogin 'Accounts', 'Mary'

  

B. Remove the default login mapping

This example removes the default login mapping originally created by executing sp_addlinkedserver on the local server Accounts.

EXEC sp_droplinkedsrvlogin 'Accounts', NULL

  

See Also
Security for Linked Servers sp_addlinkedsrvlogin
sp_addlinkedserver System Stored Procedures

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.