sp_dropserver

Drops a server from the list of known servers or drops remote logins and external logins in the same operation.

Syntax

sp_dropserver server [, droplogins]

Parameters

Examples

Usage

There are additional considerations when using sp_dropserver:
  • Executing sp_dropserver drops a server from the list of known servers by deleting the entry from the master.dbo.sysservers table.

  • Running sp_dropserver on a server that has associated entries in the master.dbo.sysremotelogins table results in an error message stating that you must drop the remote users before you can drop the server. To drop all remote logins for a server when dropping the server, use droplogins.

  • Running sp_dropserver without droplogins against a server that has associated entries in the sysattributes table results in an error. You must drop the remote logins and external logins before you can drop the server.

  • The checks against sysattributes for external logins and for default mapping to a server apply when Component Integration Services is configured.

Permissions

The permission checks for sp_dropserver differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage server privilege.

When droplogins is specified, you must be a user with manage any remote login privilege.

SAP ASE high availablility – You must be a user with manage server privilege and ha_role. When droplogins is specified, you must be a user with manage any remote login privilege.

SAP ASE shared-disk cluster – You must be a user with manage server and manage cluster privileges. When droplogins is specified, you must be a user with manage any remote login privilege.

Disabled

With granular permissions disabled, you must be a user with sso_role.

SAP ASE high availablility – You must be a user with sso_role permission and ha_role.

SAP ASE shared-disk cluster – You must be a user with sso_role and sa_role permission.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addserver
sp_dropremotelogin
sp_helpremotelogin
sp_helpserver