Thursday, September 16, 2010

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!


Kimberley said...

Thanks for your post. I already have my list of orphaned users due to a similar scenario, but I'm not certain how to get rid of them.

I cannot delete them through the front end in GP. I get the message that "Deleting the login failed for an unknown reason. Contact your SQL Server administrator for assistance". Do I simply have to delete them out of the table SY01400 through SQL server? Are there additional tables? Is there any truth to the rumor that this can delete valuable history?

Thanks for your help!

Steve Endow said...

Hi Kimberly,

To resolve the issue with the orphaned users, you can follow the steps on MBS KB Article 943027.

Let me know if you can access that article on Customer Source and if the instructions resolved your issue.


Steve Endow

Kimberley said...

Hi Steve,

Thanks for the KB #. My CustomerSource account is limited to SL access, and this particular client doesn't have a CustomerSource account anymore, so I can only search GP KB articles if I know the KB #.

Yes, this worked. I had to use the second method (cannot be dropped from the database), because the first yielded an error that the login didn't exist. The second method worked beautifully.

Thanks again for your help!

Dave Drlich said...

Hi Steve

I know this is an older thread, but even if you capture the logins, it's likely you would still run into issues with the passwords based on the password encryption and the difference in the server names, correct?