sp_validatelogins

Transact-SQL Reference

Transact-SQL Reference

sp_validatelogins

Reports information about orphaned Microsoft® Windows NT® users and groups that no longer exist in the Windows NT environment but still have entries in the Microsoft SQL Server™ system tables.

Syntax

sp_validatelogins

Return Code Values

0 (success) or 1 (failure)

Result Sets
Column name Data type Description
SID varbinary(85) Windows NT security identifier of the Windows NT user or group.
NT Login sysname Name of the Windows NT user or group.
Remarks

The entries in the system tables for the orphaned Windows NT users and groups can only be removed by using sp_revokelogin. If the Windows NT user or group has a user account in a database, the user account can be removed using sp_revokedbaccess. The user account in the database must be removed before the login can be revoked access to connect to SQL Server.

If the Windows NT user or group owns objects in a database, these objects must be removed, or their ownership must be given to another user using sp_changeobjectowner.

Permissions

Only members of the sysadmin or securityadmin fixed server roles can execute sp_validatelogins.

Examples

This example displays the Windows NT users and groups that no longer exist but are still granted access to connect to SQL Server.

EXEC sp_validatelogins
GO

SID                                                        NT Login
---------------------------------------------------------- -----------
0x0105000000000005150000007961275C521FE65395177650FC030000 dom\andrew
0x0105000000000005150000007961275C521FE65395177650FA030000 dom\joe
0x0105000000000005150000007961275C521FE65395177650FB030000 dom\margaret
0x0105000000000005150000007961275C521FE65395177650F3030000 dom\mike

See Also

sp_changeobjectowner

sp_revokedbaccess

sp_revokelogin

System Stored Procedures