sp_revokelogin (Transact-SQL)

[ @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 SQL Server 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 SQL Server 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 SQL Server. 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 SQL Server, 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]

Reference

Help and Information