Any suggestions? Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize


SQL Server 2000

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'


[@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)


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 and securityadmin fixed server roles can execute sp_droplinkedsrvlogin.

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



System Stored Procedures

© 2016 Microsoft