Blog

The system stored procedure sp_validatelogins returns a list of logins in an SQL Server instance that no longer exists in the windows environment, for instance in the AD or on the local computer.

The list of orphaned users can be useful in a couple of scenarios.

When you execute the procedure it will return a list of users no longer found in the environment. I created a user on my laptop, added it as login in SQL Server, and dropped the account. The orphan user is now found by sp_validatelogins:

The procedure can be used to automatically delete orphaned users on a scheduled interval, but personally I’d to this in a manual process instead.

Another use for the procedure is to use in a log shipping scenario, or any other scenario where users are transferred (maybe as part of restore on a different server). The procedure can be used to filter out orphaned users at setup. This way you’ll avoid getting an error trying to create a login on an account that no longer exists.

Probably there are other uses as well, and to use the result from the procedure you’ll need to get it into a table. To do that, use the following script:

IF OBJECT_ID('tempdb..#TMP_SP_VALIDATELOGINS') IS NOT NULL BEGIN
	DROP TABLE #TMP_SP_VALIDATELOGINS
END

CREATE TABLE #TMP_SP_VALIDATELOGINS
	(
		COL_SID varbinary(85) NOT NULL
		, COL_NT_Login SYSNAME NOT NULL
	)

INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins

/*
Do something useful with the table...
*/

DROP TABLE #TMP_SP_VALIDATELOGINS
GO