Muokkaa

Jaa


sp_revokelogin (Transact-SQL)

Applies to: SQL Server

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 SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use DROP LOGIN instead.

Transact-SQL syntax conventions

Syntax

sp_revokelogin [ @loginame = ] N'loginame'
[ ; ]

Arguments

[ @loginame = ] N'loginame'

The name of the Windows user or group. @loginame is sysname, with no default. @loginame can be any existing Windows user name or group in the form <ComputerName>\<User> or <Domain>\<User>.

Return code values

0 (success) or 1 (failure).

Remarks

sp_revokelogin disables connections using the account specified by @loginame. Windows users that are 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 @loginame specifies the name of a Windows group, members of that group that have been separately granted access to the instance of SQL Server can still 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:

EXEC sp_revokelogin [ADVWORKS\john]

User ADVWORKS\john can still connect if ADVWORKS\Admins is 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 can't 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];