sp_validatelogins (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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).|

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_validatelogins  

Return Code Values

0 (success) or 1 (failure)

Result Sets

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

Remarks

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.

Permissions

Requires membership in the sysadmin or securityadmin fixed server role.

Examples

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  

See Also

System Stored Procedures (Transact-SQL)
Security Stored Procedures (Transact-SQL)
DROP USER (Transact-SQL)
DROP LOGIN (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)