Removes the login entries from Microsoft® SQL Server™ for a Microsoft Windows NT® user or group created with sp_grantlogin or sp_denylogin.
sp_revokelogin [@loginame =] 'login'
0 (success) or 1 (failure)
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.
Only members of the sysadmin or securityadmin fixed server roles can execute sp_revokelogin.
This example removes the login entries for the Windows NT user Corporate\MollyA.
EXEC sp_revokelogin 'Corporate\MollyA'
Or
EXEC sp_revokelogin [Corporate\MollyA]
sp_denylogin | sp_grantlogin |
sp_droplogin | System Stored Procedures |