Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.
Applies to: SQL Server (SQL Server 2008 through current version).
If the orphaned server-level principal owns a database user, the database user must be removed before the orphaned server principal can be removed. To remove a database user, use DROP USER. If the server-level principal owns securables in the database, ownership of the securables must be transferred or they must be dropped. To transfer ownership of database securables, use ALTER AUTHORIZATION.
To remove mappings to Windows users and groups that no longer exist, use DROP LOGIN.
The following example displays the Windows users and groups that no longer exist but are still granted access to an instance of SQL Server.
EXEC sp_validatelogins; GO