Revert “contained database authentication” after Installation XP1

Question

We have installed sitecore XP1 on our Servers and our SQL admin whats to disable the "contained database authentication" after installation because of security issues he was reading in a article from MS.

I've also seen this article: Why do we require Contained database for Sitecore 9?

So here the Question

How can I revert the contained Database setting after the installation without destroying my Sitecore instance?

We have allready tried to extract the Users and create local Server Logins for them but that doesn't seem to work for all Sitecore users.

When we try to extract the sitecore_coreuser with this SQL Script:

    USE Sitecore_Core

    ALTER USER [sitecore_coreuser] WITH  NAME = [sitecore_coreuser_ORIG]
    USE master
    CREATE LOGIN [sitecore_coreuser] WITH PASSWORD=N'sitecore_coreuser'
    USE Sitecore_Core
    CREATE USER [sitecore_coreuser] FOR LOGIN [sitecore_coreuser] WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [aspnet_Membership_BasicAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Membership_FullAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Membership_ReportingAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Profile_BasicAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Profile_FullAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Profile_ReportingAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Roles_BasicAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Roles_FullAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Roles_ReportingAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [db_datareader] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [db_datawriter] ADD MEMBER [sitecore_coreuser]
    GO

Sitecore is not working anymore. Did we miss something in the usersettings?

Answer 1

Our SQL Admin has created a SQL Script to remove the local DB accounts and create Accounts for the server the script was created for sitecore 9.0.2

That seems to work, but we are not using this in production right now.

    USE Sitecore_Core
    DROP USER sitecore_coreuser
    USE master
    CREATE LOGIN sitecore_coreuser WITH PASSWORD=N'sitecore_coreuser'
    USE Sitecore_Core
    CREATE USER sitecore_coreuser FOR LOGIN sitecore_coreuser WITH DEFAULT_SCHEMA=dbo
    ALTER ROLE [aspnet_Membership_BasicAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Membership_FullAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Membership_ReportingAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Profile_BasicAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Profile_FullAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Profile_ReportingAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Roles_BasicAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Roles_FullAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [aspnet_Roles_ReportingAccess] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [db_datareader] ADD MEMBER [sitecore_coreuser]
    ALTER ROLE [db_datawriter] ADD MEMBER [sitecore_coreuser]
    GRANT EXECUTE TO sitecore_coreuser
    GO

    USE Sitecore_Master
    DROP USER sitecore_masteruser
    USE master
    CREATE LOGIN sitecore_masteruser WITH PASSWORD=N'sitecore_masteruser'
    USE Sitecore_Master
    CREATE USER sitecore_masteruser FOR LOGIN sitecore_masteruser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE db_datareader ADD MEMBER sitecore_masteruser
    ALTER ROLE db_datawriter ADD MEMBER sitecore_masteruser
    GRANT EXECUTE TO sitecore_masteruser
    GO

    USE Sitecore_Web
    DROP USER sitecore_webuser
    USE master
    CREATE LOGIN sitecore_webuser WITH PASSWORD=N'sitecore_webuser'
    USE Sitecore_Web
    CREATE USER sitecore_webuser FOR LOGIN sitecore_webuser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_webuser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_webuser
    GO

    USE Sitecore_ExperienceForms
    DROP USER sitecore_formsuser
    USE master
    CREATE LOGIN sitecore_formsuser WITH PASSWORD=N'sitecore_formsuser'
    USE Sitecore_ExperienceForms
    CREATE USER sitecore_formsuser FOR LOGIN sitecore_formsuser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_formsuser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_formsuser
    GRANT EXECUTE TO sitecore_formsuser
    GO

    USE [Sitecore_EXM.Master]
    DROP USER sitecore_exmmasteruser
    USE master
    CREATE LOGIN sitecore_exmmasteruser WITH PASSWORD=N'sitecore_exmmasteruser'
    USE [Sitecore_EXM.Master]
    CREATE USER sitecore_exmmasteruser FOR LOGIN sitecore_exmmasteruser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_exmmasteruser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_exmmasteruser
    GRANT EXECUTE TO sitecore_exmmasteruser
    GO

    USE Sitecore_Reporting
    DROP USER sitecore_reportinguser
    USE master
    CREATE LOGIN sitecore_reportinguser WITH PASSWORD=N'sitecore_reportinguser'
    USE Sitecore_Reporting
    CREATE USER sitecore_reportinguser FOR LOGIN sitecore_reportinguser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_reportinguser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_reportinguser
    GRANT EXECUTE TO sitecore_reportinguser
    GO

    USE [Sitecore_Processing.Tasks]
    DROP USER sitecore_tasksuser
    USE master
    CREATE LOGIN sitecore_tasksuser WITH PASSWORD=N'sitecore_tasksuser'
    USE [Sitecore_Processing.Tasks]
    CREATE USER sitecore_tasksuser FOR LOGIN sitecore_tasksuser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_tasksuser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_tasksuser
    GRANT EXECUTE TO sitecore_tasksuser
    GO

    USE Sitecore_MarketingAutomation
    DROP USER sitecore_marketingautomationuser
    USE master
    CREATE LOGIN sitecore_marketingautomationuser WITH PASSWORD=N'sitecore_marketingautomationuser'
    USE Sitecore_MarketingAutomation
    CREATE USER sitecore_marketingautomationuser FOR LOGIN sitecore_marketingautomationuser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_marketingautomationuser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_marketingautomationuser
    GRANT EXECUTE TO sitecore_marketingautomationuser
    GO

    USE Sitecore_Messaging
    DROP USER sitecore_messaginguser
    USE master
    CREATE LOGIN sitecore_messaginguser WITH PASSWORD=N'sitecore_messaginguser'
    USE Sitecore_Messaging
    CREATE USER sitecore_messaginguser FOR LOGIN sitecore_messaginguser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_messaginguser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_messaginguser
    GRANT EXECUTE TO sitecore_messaginguser
    GO

    USE [Sitecore_Processing.Pools]
    DROP USER sitecore_poolsuser
    USE master
    CREATE LOGIN sitecore_poolsuser WITH PASSWORD=N'sitecore_poolsuser'
    USE [Sitecore_Processing.Pools]
    CREATE USER sitecore_poolsuser FOR LOGIN sitecore_poolsuser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_poolsuser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_poolsuser
    GRANT EXECUTE TO sitecore_poolsuser
    GO

    USE Sitecore_ReferenceData
    DROP USER sitecore_referencedatauser
    USE master
    CREATE LOGIN sitecore_referencedatauser WITH PASSWORD=N'sitecore_referencedatauser'
    USE Sitecore_ReferenceData
    CREATE USER sitecore_referencedatauser FOR LOGIN sitecore_referencedatauser WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_datareader] ADD MEMBER sitecore_referencedatauser
    ALTER ROLE [db_datawriter] ADD MEMBER sitecore_referencedatauser
    GRANT EXECUTE TO sitecore_referencedatauser
    GO

    USE [Sitecore_Xdb.Collection.ShardMapManager]
    -- NICHTS ZU MACHEN:
    -- es ist kein DB-Contained Login!
    GO

    USE [Sitecore_Xdb.Collection.Shard0]
    -- NICHTS ZU MACHEN:
    -- es ist kein DB-Contained Login!
    GO

    USE [Sitecore_Xdb.Collection.Shard1]
    -- NICHTS ZU MACHEN:
    -- es ist kein DB-Contained Login!
    GO



    USE Sitecore_Core
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_Core SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_Core SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_Core SET MULTI_USER WITH NO_WAIT
    GO  

    USE Sitecore_ExperienceForms
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_ExperienceForms SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_ExperienceForms SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_ExperienceForms SET MULTI_USER WITH NO_WAIT
    GO  

    -- !
    USE Sitecore_MarketingAutomation;
    DECLARE @dbname sysname;
    SET @dbname = 'Sitecore_MarketingAutomation';
    DECLARE @spid INT;
    SELECT @spid = MIN(SPID) FROM sys.sysprocesses
    WHERE dbid = DB_ID(@dbname) AND SPID != @@SPID;
    WHILE @spid IS NOT NULL
    BEGIN
            EXECUTE ('Kill ' + @spid);
            SELECT @spid = MIN(SPID) FROM sys.sysprocesses
            WHERE dbid = DB_ID(@dbname) AND SPID != @@SPID;
    END;
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_MarketingAutomation SET SINGLE_USER WITH NO_WAIT;
    ALTER DATABASE Sitecore_MarketingAutomation SET CONTAINMENT = NONE WITH NO_WAIT;
    ALTER DATABASE Sitecore_MarketingAutomation SET MULTI_USER WITH NO_WAIT;
    GO  



    USE Sitecore_Master
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_Master SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_Master SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_Master SET MULTI_USER WITH NO_WAIT
    GO  

    USE Sitecore_Messaging
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_Messaging SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_Messaging SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_Messaging SET MULTI_USER WITH NO_WAIT
    GO  

    USE Sitecore_ReferenceData
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_ReferenceData SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_ReferenceData SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_ReferenceData SET MULTI_USER WITH NO_WAIT
    GO  

    USE Sitecore_Reporting
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_Reporting SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_Reporting SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_Reporting SET MULTI_USER WITH NO_WAIT
    GO  

    USE Sitecore_Web
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE Sitecore_Web SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE Sitecore_Web SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE Sitecore_Web SET MULTI_USER WITH NO_WAIT
    GO  

    USE [Sitecore_EXM.Master]
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE [Sitecore_EXM.Master] SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE [Sitecore_EXM.Master] SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE [Sitecore_EXM.Master] SET MULTI_USER WITH NO_WAIT
    GO  

    USE [Sitecore_Processing.Pools]
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE [Sitecore_Processing.Pools] SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE [Sitecore_Processing.Pools] SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE [Sitecore_Processing.Pools] SET MULTI_USER WITH NO_WAIT
    GO  

    USE [Sitecore_Processing.Tasks]
    -- CONTAIN LOGIN r�ckg�ngig mit Force
    ALTER DATABASE [Sitecore_Processing.Tasks] SET SINGLE_USER WITH NO_WAIT
    ALTER DATABASE [Sitecore_Processing.Tasks] SET CONTAINMENT = NONE WITH NO_WAIT
    ALTER DATABASE [Sitecore_Processing.Tasks] SET MULTI_USER WITH NO_WAIT
    GO  

    --USE [Sitecore_Xdb.Collection.Shard0]
    --USE [Sitecore_Xdb.Collection.Shard1]
    --USE [Sitecore_Xdb.Collection.ShardMapManager]