sp_droplinkedsrvlogin
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 and 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