Mini Post: Detecting Orphaned Users; sp_change_users_logins

Good day all! We are all the way in October 2018 and we are due for another SQL Something! Man is the year flying by!

Today, we are looking at detecting orphaned users. In short, these are usually users that do not have an associated SQL login.

You can detect these 'orphans' using the sp_change_users_login stored procedure with the @Action='Report' option.





Fig 1. shows what would happen if I run the command on a database with no orphaned users. We get no results:

Fig. 1: All users have a 'parent'.

I then delete the associated login, thereby creating an orphaned user:

Fig. 2: No more 'parent'. User has been orphaned.

 Now I rerun the stored procedure and we can see the newly orphaned user appear in the results:

Fig. 3: Poor Robin.

And that's it. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)