Finding Invalid Domain Logins in SQL Server - Windows 10 laptop version

SQL Server provides a built in stored procedure that identifies invalid domain logins (or groups),  which are those that exist on the SQL instance as logins but don't exist on the domain - sp_validatelogins, here's the official description:

Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.

In order to test this on my laptop I'm going to setup a new local account in Windows (I'm using Windows 10 still btw) so to begin with I'll go to Settings and then Family & Other Users.

Then under Other Users at the bottom of the screen select + Add someone else to this PC

This will display a Microsoft sign-in box but as we're just creating a test user on my laptop I'll click the "I don't have this person's sign-in information" link which will give me another box asking me to create a Microsoft account, which I don't want to do, so I'll go ahead and click "Add a user without a Microsoft account" which will (finally) let me create a local user on my laptop, along with password and three security questions.

That's a really long way of doing something simple like creating a new local user, fortunately in PowerShell things are much more straightforward and I can just use the following code to quickly create the new TestUser local user on my laptop: 

New-LocalUser -Name "TestUser" -Description "Just a test" -NoPassword

I could set a password here too but I'm using -NoPassword as I'm going to delete the login once it's setup in SQL Server to test the sp_validatelogins stored procedure, after running the code the following message is displayed showing that the user has been created:

Name     Enabled Description

----     ------- -----------

TestUser True    Just a test

To create the corresponding login in SQL Server I'll run the following T-SQL in Management Studio (DESKTOP-MFKSHR7 is my machine name):

USE [master]

GO

CREATE LOGIN [DESKTOP-MFKSHR7\TestUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

And the login is created! It's worth adding at this point that if I tried to run the T-SQL code without creating the local user first then I'd receive the following error message: "Windows NT user or group 'DESKTOP-MFKSHR7\TestUser' not found. Check the name again."

If I run EXEC sp_validatelogins now then the stored procedure won't return any rows as my login is a valid account in Windows so I need to now delete the local Windows account I just created earlier. This time I'll go straight to PowerShell and run the following:

Remove-LocalUser -Name "TestUser"

This has removed the account from my machine but the login will remain in SQL Server, which is great because that's the whole point of this article! Now I can run the sp_validatelogins stored procedure again (EXEC sp_validatelogins) and this time a row containing the TestUser login name and it's SID are returned indicating it's an invalid account:

What we don't do straightaway is drop the login as we need to unsure that any potential object ownership issues are resolved first and that database users for that login are removed first, here's the advice from Microsoft:

A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.

You can drop a login to which database users are mapped; however, this will create orphaned users.