DBATools issue 5887 SQl side test code

CreateOrphanedContainedUsersTest.sql

/* Set the sysconfig setting to support contained databases if not already set - assumes SQL 2012 or newer */
if (select value_in_use from sys.configurations where name = 'contained database authentication') <> 1
begin
	exec sp_configure 'contained database authentication', 1;
	reconfigure
end
GO

/* Create the ContainedTest database where we'll be doing the work */
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ContainedTest')
BEGIN
	CREATE DATABASE ContainedTest CONTAINMENT = PARTIAL;
	ALTER DATABASE ContainedTest SET RECOVERY SIMPLE;
END
GO

/* Create the instance level logins that we will give access to the contained database */
USE Master;
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Uncontained1')
	CREATE LOGIN Uncontained1 WITH PASSWORD = '@password1'; /* Normal login we won't be doing anything with */

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDelete')
	CREATE LOGIN UncontainedToDelete WITH PASSWORD = '@password1'; /* Login we will delete once the user is created for it */

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToHandleRename')
	CREATE LOGIN UncontainedToHandleRename WITH PASSWORD = '@password1'; /* Login that will have a different user in DB but SIDs will match */

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDropAdd')
	CREATE LOGIN UncontainedToDropAdd WITH PASSWORD = '@password1'; /* Login we will drop, then readd to get a different SID */
GO


/* Create users for the logins in ContainedTest */
USE ContainedTest;
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Uncontained1')
	CREATE USER Uncontained1 FOR LOGIN Uncontained1;

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'UncontainedToDelete')
	CREATE USER UncontainedToDelete FOR LOGIN UncontainedToDelete;

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'RenamedUncontained3')
	CREATE USER RenamedUncontained3 FOR LOGIN UncontainedToHandleRename; /* Note the user has a different name than the login */

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'UncontainedToDropAdd')
	CREATE USER UncontainedToDropAdd FOR LOGIN UncontainedToDropAdd;

/* Create a contained user */
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'ContainedLogin')
	CREATE USER ContainedLogin WITH PASSWORD = '@password1';
GO

/* Remove the login for UncontainedToDelete which will orphan the user */
USE master;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDelete')
	DROP LOGIN UncontainedToDelete;
GO

/* Remove and readd the login for UncontainedToDropAdd so that it gets a different SID (orphaned user with a login available) */
USE master;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDropAdd')
	DROP LOGIN UncontainedToDropAdd;
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDropAdd')
	CREATE LOGIN UncontainedToDropAdd WITH PASSWORD = '@password1'; 
GO

/* Check what users exist in the database - should be 4 regular and 1 contained */
/* (note SIDs will be different on your instance)
Name					sid															authentication_type_desc
Uncontained1			0xFB135DABD976F749BE3AE0B794B773C1							INSTANCE
UncontainedToDelete		0x96099BC6D88F3E48B13F372FA8C3238C							INSTANCE
RenamedUncontained3		0x7DE2B171F4A38346A4F87060FF6A3382							INSTANCE
UncontainedToDropAdd	0xB71F18EB8E0F1C49B28A796ADB30C241							INSTANCE
ContainedLogin			0x01050000000000090300000075F614C84C94634FB48E4164C50EE51D	DATABASE
*/
USE ContainedTest;
SELECT Name, sid, authentication_type_desc 
FROM sys.database_principals
WHERE type = 'S' and authentication_type > 0;

/* Confirm that we have orphaned users */
exec sp_change_users_login 'Report';

/* Cleanup by dropping the database and logins */
/*
USE master;
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ContainedTest')
BEGIN
	DROP DATABASE ContainedTest;
END
GO

USE Master;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Uncontained1')
	DROP LOGIN Uncontained1;

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDelete')
	DROP LOGIN UncontainedToDelete;

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToHandleRename')
	DROP LOGIN UncontainedToHandleRename;

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UncontainedToDropAdd')
	DROP LOGIN UncontainedToDropAdd;
GO
*/