Removes an existing mapping between a login on the local server running Microsoft® SQL Server™ and a login on the linked server.
sp_droplinkedsrvlogin [@rmtsrvname =] 'rmtsrvname',
[@locallogin =] 'locallogin'
0 (success) or 1 (failure)
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.
Only members of the sysadmin or securityadmin fixed server roles can execute sp_droplinkedsrvlogin.
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'
This example removes the default login mapping originally created by executing sp_addlinkedserver on the local server Accounts.
EXEC sp_droplinkedsrvlogin 'Accounts', NULL
Security for Linked Servers | sp_addlinkedsrvlogin |
sp_addlinkedserver | System Stored Procedures |