sp_revokelogin
Removes the login entries from Microsoft® SQL Server™ for a Microsoft Windows NT® user or group created with sp_grantlogin or sp_denylogin.
Syntax
sp_revokelogin [ @loginame = ] 'login'
Arguments
[@loginame =] 'login'
Is the name of the Windows NT user or group. login is sysname, with no default. login can be any existing Windows NT username or group in the form Domain\User.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_revokelogin does not explicitly prevent Windows NT users from connecting to SQL Server, but prevents Windows NT users from doing so through their Windows NT user accounts. However, Windows NT users can still connect if they are members of a Windows NT group that has been granted access to SQL Server using the sp_grantlogin stored procedure. For example, if Windows NT user REDMOND\john is a member of the Windows NT group REDMOND\Admins, and REDMOND\john is revoked access using:
sp_revokelogin [REDMOND\john]
REDMOND\john can still connect if REDMOND\Admins is granted access. Similarly, if REDMOND\Admins is revoked access but REDMOND\john is granted access, REDMOND\john can still connect.
Use sp_denylogin to explicitly prevent users from connecting with SQL Server, regardless of their Windows NT group memberships.
Use sp_droplogin to remove a SQL Server login added with sp_addlogin.
sp_revokelogin cannot be executed within a user-defined transaction.
Permissions
Only members of the sysadmin or securityadmin fixed server roles can execute sp_revokelogin.
Examples
This example removes the login entries for the Windows NT user Corporate\MollyA.
EXEC sp_revokelogin 'Corporate\MollyA'
Or
EXEC sp_revokelogin [Corporate\MollyA]