This topic has not yet been rated - Rate this topic

sp_dropremotelogin (Transact-SQL)

SQL Server 2012

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 the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use linked servers and linked-server stored procedures instead.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_dropremotelogin [ @remoteserver = ] 'remoteserver' 
     [ , [ @loginame = ] 'login' ] 
     [ , [ @remotename = ] 'remote_name' ]

Arguments

[ @remoteserver = ] 'remoteserver': Is the name of the remote server mapped to the remote login that is to be removed. remoteserver is sysname, with no default. remoteserver must already exist.
[ @loginame = ] 'login': Is the optional login name on the local server that is associated with the remote server. login is sysname, with a default of NULL. login must already exist if specified.
[ @remotename = ] 'remote_name': Is the optional name of the remote login that is mapped to login when logging in from the remote server. remote_name is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

If only remoteserver is specified, all remote logins for that remote server are removed from the local server. If login is also specified, all remote logins from remoteserver 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 remoteserver 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 version 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 cannot be executed within a user-defined transaction.

Permissions

Requires membership in the sysadmin or securityadmin fixed server roles.

Examples

A. Dropping 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. Dropping 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. Dropping 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'

Reference

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)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD