sp_validatelogins (T-SQL)

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

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