sp_droplinkedsrvlogin (Transact-SQL)
Applies to:
SQL Server
Removes an existing mapping between a login on the local server running SQL Server, and a login on the linked server.
Transact-SQL syntax conventions
Syntax
sp_droplinkedsrvlogin
[ @rmtsrvname = ] N'rmtsrvname'
, [ @locallogin = ] N'locallogin'
[ ; ]
Arguments
[ @rmtsrvname = ] N'rmtsrvname'
The name of a linked server that the SQL Server login mapping applies to. @rmtsrvname is sysname, with no default.
[ @locallogin = ] N'locallogin'
The SQL Server login on the local server that's 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 it connects 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 were explicitly given a login mapping to the linked server, by using sp_addlinkedsrvlogin
, can access the linked server.
sp_droplinkedsrvlogin
can't be executed from within a user-defined transaction.
Permissions
Requires ALTER ANY LOGIN
permission on the server.
Examples
A. Remove the login mapping for an existing user
The following example removes the mapping for the login Mary
from the local server to the linked server Accounts
. Therefore, login Mary
uses the default login mapping.
EXEC sp_droplinkedsrvlogin 'Accounts', 'Mary';
B. Remove the default login mapping
The following example removes the default login mapping originally created by executing sp_addlinkedserver
on the linked server Accounts
.
EXEC sp_droplinkedsrvlogin 'Accounts', NULL;