sp_dropremotelogin (Transact-SQL)
Applies to:
SQL Server
Removes a remote login mapped to a local login used to execute remote stored procedures against the local server running SQL Server.
Important
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use linked servers and linked-server stored procedures instead.
Transact-SQL syntax conventions
Syntax
sp_dropremotelogin
[ @remotename = ] N'@remotename'
[ , [ @loginame = ] N'loginame' ]
[ , [ @remotename = ] N'remotename' ]
[ ; ]
Arguments
[ @remotename = ] N'@remotename'
The name of the remote server mapped to the remote login that is to be removed. @remotename is sysname, with no default. @remotename must already exist.
[ @loginame = ] N'loginame'
The optional login name on the local server that is associated with the remote server. @loginame is sysname, with a default of NULL
. @loginame must already exist if specified.
[ @remotename = ] N'remotename'
The optional name of the remote login that is mapped to @loginame when logging in from the remote server. @remotename is sysname, with a default of NULL
.
Return code values
0
(success) or 1
(failure).
Remarks
If only @remotename is specified, all remote logins for that remote server are removed from the local server. If @loginame is also specified, all remote logins from @remotename mapped to that specific local login are removed from the local server. If remote_name is also specified, only the remote login for that remote user from @remotename is removed from the local server.
To add local server users, use sp_addlogin
. To remove local server users, use sp_droplogin
.
Remote logins are required only when you use earlier versions of SQL Server. SQL Server 7.0 and later versions use linked server logins instead. Use sp_addlinkedsrvlogin
and sp_droplinkedsrvlogin
to add and remove linked server logins.
sp_dropremotelogin
can't be executed within a user-defined transaction.
Permissions
Requires membership in the sysadmin or securityadmin fixed server roles.
Examples
A. Drop all remote logins for a remote server
The following example removes the entry for the remote server ACCOUNTS
, and, therefore, removes all mappings between logins on the local server and remote logins on the remote server.
EXEC sp_dropremotelogin 'ACCOUNTS';
B. Drop a login mapping
The following example removes the entry for mapping remote logins from the remote server ACCOUNTS
to the local login Albert
.
EXEC sp_dropremotelogin 'ACCOUNTS', 'Albert';
C. Drop a remote user
The following example removes the login for the remote login Chris
on the remote server ACCOUNTS
that was mapped to the local login salesmgr
.
EXEC sp_dropremotelogin 'ACCOUNTS', 'salesmgr', 'Chris';
Related content
- Security stored procedures (Transact-SQL)
- sp_addlinkedsrvlogin (Transact-SQL)
- sp_addlogin (Transact-SQL)
- sp_addremotelogin (Transact-SQL)
- sp_addserver (Transact-SQL)
- sp_droplinkedsrvlogin (Transact-SQL)
- sp_droplogin (Transact-SQL)
- sp_helpremotelogin (Transact-SQL)
- System stored procedures (Transact-SQL)