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