Orphaned logins in SQL Server 2005

Seems like all of my posts lately have been on the failures and shortcomings of SQL Server. Actually I am very happy with SQL Server 2005, and even happier with SQL Server 2008. One problem that database practitioners have always had is that when moving databases from one server to another, the SQL logins get orphaned; the SID in the database for the user doesn't match the SID for the user in master unless you restore master of course. However, I almost never restore master since it would undoubtedly hack up the dev or test environment. For SQL Logins, Microsoft came out with the sp_change_users_logins which is fine for...SQL Logins. You have to map those individually unless you have a handy script such as this one as written by Tom Davis of Sogeti USA LLC in Irving, TX:

Beginning in SQL Server 2005, you now can map both SQL and Windows logins, SQL logins using sp_change_users_logins, and Windows using ALTER USER. What I came up with today, though, takes the cake. Let me give you the background. 

On our team we are doing a conversion of two databases for a new release of a product, and are using SSIS to populate all tables. This involves Integration Services fetching data from production, adding the new objects, attributes, FKs, schema, etc. and then repopulating the tables on the dev box. In order to keep the team moving forward, I had the brilliant idea to create the two new database versions in parallel while keeping their previous counterparts online. After the population I simply suffix the current versions with _old, and remove the _conversion for the ones that I just created. Works perfectly, and launch day will be a tremendous hit! Unfortunately, there is one gotcha - I get orphaned users.  This is where it gets good, so hang with me for a minute.

So I run a few TSQL commands and to my surprise never get the users to work. WTH? I get people standing by tapping their toes, needing the users to be able to log in and have permissions (actually they can log in but have no access), so I do the quick fix and drop the schema, remove the user and add the users back in to the database. Run a few more scripts, scratch my head, Google! a bit, and I'm stumped. I notice that while opening Security tab at the server level I see that the user does not have access to the database, but I do see that the user inside of the database (we're talking fixed database roles here, sorry for not mentioning) at the database security level.  Back to the Security tab at the server level, I click Map, add permissions in the bottom pane for "Database Role Membership for: (databasename)", close the window, and reopen it only to find that the Map check that I just added is gone! Vanished! As if I never checked the thing, it won't let me save the mapping or permissions.

Still stumped, I do a compare on the SID for master and for the database and it's the same.  Now I know that it's not a SID problem and apparently now not a typical orphaned user problem. Going through a myriad of gyrations, I came upon the idea to run CREATE USER in the database, why I have no idea. Running this and expecting to see either "user created" or "user already exists" I get nothing. Zero, zip, zilch, nada, nothing... no confirmation of any kind in the query window indicating that I either succeeded or failed.  And now the kicker - much to my surprise, voila, the user mapping at the server level has a check, the permissions are not "greyed-out", and the user now has access to the database. I verify by doing a Run As on one of the test windows accounts that I have, and find that it works perfectly.

Here's the modification to the above script so you'll have both handy if you ever happen to run across this mess. Otherwise, let me know if you have run into the same thing.


Lee

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