CREATE PROCEDURE dbo.GDPRSetPartialMode AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, November 2017
  Set containment to partial - for enable to create users in db.
*/

DECLARE @Containment BIT
DECLARE @DbName VARCHAR(1000)
DECLARE @ver VARCHAR(MAX)
DECLARE @s nvarchar(500)
DECLARe @SingleMode VARCHAR(500)

SELECT @DbName = DB_NAME()
SET @Containment = 0
SELECT @ver = CAST(SERVERPROPERTY('productversion') AS VARCHAR(100))
IF CAST(SUBSTRING(@ver, 0, CHARINDEX('.', @ver)) AS INT) >= 13 -- SQL Server 2016 or newer
BEGIN
  SELECT @Containment = CAST(CAST(DATABASEPROPERTYEX(@DbName, 'containment') AS INT) AS BIT)
  SELECT @SingleMode = user_access_desc FROM sys.databases WHERE name = @DbName

  IF @Containment = 0 AND @SingleMode = 'SINGLE_USER'
  BEGIN
    SET @s = 'ALTER DATABASE [' + @DbName + '] SET CONTAINMENT = PARTIAL WITH NO_WAIT'
    EXEC sp_executesql @s    

    SELECT @Containment = CAST(CAST(DATABASEPROPERTYEX(@DbName, 'containment') AS INT) AS BIT)
    IF @Containment = 0
      PRINT 'ERROR'
  END
END