Reversing or disabling Contained Databases in Microsoft SQL Server 2012

I'm no SQL MVP, so I'm not here to tell you the correct way to recover from a failed attempt at enabling SQL Server Contained Databases, but I will tell you about what I experienced.

I started by looking for a way to use this with AlwaysOn and my Infor Visual Manufacturing database on SQL 2012 Enterprise Edition (Required for AlwaysOn) so that my users would show up in the secondary database servers without having to copy their profiles from server to server using tools that export the Hash and SID, like sp_help_revlogin (Look into this! It works great and is a safer alternative).

Turns out that this was a bad idea.  I tested it in a lab and it worked fine, but I didn't test every function and I found that Info Visual Manufacturing ERP has a collation error in the Audit Maintenance window.  This was the error:

Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

There is almost no information about this error and since I can't change the source code for my ERP system, I had to revert to a non-contained database.  I could find no information on changing my SQL database from a partially contained database back to none, so I used trial and error to get things back.

Problem 1.  You cannot just change your database to non-contained.  SQL will complain that a non-contained database cannot have users with passwords. You will have to drop all your users before you can turn off containment.  I used SQL to create the DROP and CREATE scripts before I deleted the users.  Run the Drop first, turn off containment and then run the create.

Problem 2: Now the new users have no security settings!  In Infor Visual Manufacturing I had to delete every user and then re-create them.  Luckily I had single sign-on activated, so I was able to just use a fake password while creating them. This took 3 people 10 hours on a Saturday to re-create 250 users.

Problem 3.  When you run the scripts to enable a partial contained database (See my other blog entry on this here) the script will disable the login for the user at the server level, so you will need to re-enable all you users.  This is the script I used to create a list of users to enable, edit the list it generates and then run that script.  (Sometimes you need to replace the quotes if you cut and paste from this page and they come across wrong)

SELECT 'ALTER LOGIN [' + DP.name + '] ENABLE;' FROM sys.server_principals DP
 WHERE DP.TYPE IN ('S')
 AND DP.is_disabled = 1;
 GO