sp_revokelogin (Transact-SQL)
Removes the login entries from SQL Server for a Windows user or group created by using CREATE LOGIN, sp_grantlogin, or sp_denylogin.
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use DROP LOGIN instead.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_revokelogin [ @loginame= ] 'login'
Arguments
- [ @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.
Return Code Values
0 (success) or 1 (failure)
Remarks
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.
Permissions
Requires ALTER ANY LOGIN permission on the server.
Examples
The following example removes the login entries for the Windows user Corporate\MollyA.
EXEC sp_revokelogin 'Corporate\MollyA'
Or
EXEC sp_revokelogin [Corporate\MollyA]