Drops a server from the list of known servers or drops remote logins and external logins in the same operation.
sp_dropserver server [, droplogins]
is the name of the server to be dropped.
indicates that any remote logins for server should also be dropped.
This command drops the remote server GATEWAY:
sp_dropserver GATEWAY
Drops the entry for the remote server RDBAM_ALPHA and drops all remote logins and external logins for that server:
sp_dropserver RDBAM_ALPHA, droplogins
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.
The permission checks for sp_dropserver differ based on your granular permissions settings.
Granular permissions 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. Adaptive Server 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. Adaptive Server 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. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sso_role. Adaptive Server high availablility – You must be a user with sso_role permission and ha_role. Adaptive Server shared-disk cluster – You must be a user with sso_role and sa_role permission. |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_addserver, sp_dropremotelogin, sp_helpremotelogin, sp_helpserver
