Identifying Orphaned SQL Server Users

Scenario 1:  Your client has a production Dynamics GP server, and decides that they want to have a separate test server for Dynamics GP.  You install SQL Server and Dynamics GP on the test server, and then you restore copies of the Dynamics database and company databases, and viola, your test server is ready.

Scenario 2:  Your client calls and frantically tells you that their Dynamics GP SQL server has died.  Fortunately, and to your surprise, they actually had good database backups from the prior night.  You replace the failed hard drives, rebuild the server, install SQL, and restore the Dynamics and company databases.

In both of these scenarios, what is a common administrative hassle that you will likely eventually run into?

Well, when you restore a database from SQL Server A onto SQL Server B, the database users will not have corresponding SQL Server logins.

For Scenario 1, there is a very handy "Capture Logins" script provided in KB 878449 that allows you to transfer the SQL logins to a new server, saving alot of time and hassle deleting users and recreating users.

For Scenario 2, if you or the client don't regularly run the Capture Logins script (I haven't met anyone who does, although it probably isn't a bad idea), then you will have a fresh SQL Server with no logins, and databases with a bunch of orphaned users.

This isn't earth shattering news or even a big deal, just a bit of a hassle.

But while studying for the painful SQL 2008 exam, I came across a system stored procedure that identifies such orphaned users.

sp_change_users_login @Action='Report';

And contrary to what I recently wrote about the SQL 2008 exam content being largely useless to the GP crowd, I actually used this script today!  A client's SQL server had died and been rebuilt, and we were having to reconstruct a very complex, horribly designed custom integration that required numerous SQL Server logins.  While trying to figure out which logins were missing on the rebuilt server, I remembered this command and gave it a try.

Sure enough, it produced a nice clean list of orphaned users, and I saw the one that I was looking for.

Sure, you could compare the SQL logins with the database users in each DB, but this command is very simple and very easy.  And if you have security conscious clients, they might want to run this to remove any orphaned users so that they have clean databases.

And there you have it.  The first semi-usable nugget that I've gleaned from studying for the SQL exam!