| title | sp_revokelogin (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | cb1ab102-1ae0-4811-9144-9a8121ef2d7e | ||
| author | VanMSFT | ||
| ms.author | vanto |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Removes the login entries from [!INCLUDEssNoVersion] for a Windows user or group created by using CREATE LOGIN, sp_grantlogin, or sp_denylogin.
Important
[!INCLUDEssNoteDepFutureAvoid] Use DROP LOGIN instead.
Transact-SQL Syntax Conventions
sp_revokelogin [ @loginame= ] 'login'
[ @loginame = ] 'login'
Is the name of the Windows user or group. login is sysname, with no default. login can be any existing Windows user name or group in the form Computer name\User or Domain\User.
0 (success) or 1 (failure)
sp_revokelogin disables connections using the account specified by the login parameter. But Windows users that have been granted access to an instance of [!INCLUDEssNoVersion] through membership in a Windows group can still connect as the group after their individual access has been revoked. Similarly, if the login parameter specifies the name of a Windows group, members of that group that have been separately granted access to the instance of [!INCLUDEssNoVersion] will still be able to connect.
For example, if Windows user ADVWORKS\john is a member of the Windows group ADVWORKS\Admins, and sp_revokelogin revokes the access of ADVWORKS\john:
sp_revokelogin [ADVWORKS\john]
User ADVWORKS\john can still connect if ADVWORKS\Admins has been granted access to an instance of [!INCLUDEssNoVersion]. Similarly, if Windows group ADVWORKS\Admins has its access revoked but ADVWORKS\john is granted access, ADVWORKS\john can still connect.
Use sp_denylogin to explicitly prevent users from connecting to an instance of [!INCLUDEssNoVersion], regardless of their Windows group memberships.
sp_revokelogin cannot be executed within a user-defined transaction.
Requires ALTER ANY LOGIN permission on the server.
The following example removes the login entries for the Windows user Corporate\MollyA.
EXEC sp_revokelogin 'Corporate\MollyA';
Or
EXEC sp_revokelogin [Corporate\MollyA];
Security Stored Procedures (Transact-SQL)
DROP LOGIN (Transact-SQL)
sp_denylogin (Transact-SQL)
sp_droplogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
System Stored Procedures (Transact-SQL)