Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

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.

ms188784.note(en-US,SQL.90).gifImportant:
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.

Topic link icon 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 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]
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.