sp_droplinkedsrvlogin

Transact-SQL Reference

Transact-SQL Reference

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

See Also

Security for Linked Servers

sp_addlinkedserver

sp_addlinkedsrvlogin

System Stored Procedures