-- Make sure containment is enabled for this test
Exec sp_configure 'contained database authentication', 1;
Reconfigure;
Go

Create Database TestAlterRole Containment = Partial;
Go

-- Make sure in full recovery model
Alter Database TestAlterRole Set Recovery Full;
Go

-- Make sure full recovery initialized
Backup Database TestAlterRole To Disk = 'c:\bak\TestAlterRole.bak' With Init;
Backup Log TestAlterRole To Disk = 'c:\bak\TestAlterRole_1.trn' With Init;
Go

-- Create a Role and User
Use TestAlterRole;

Create Role TestRole;
Create User TestUser With Password = 'Not$trongEnuff'
Go

-- Add the user to the role
Alter Role TestRole Add Member TestUser;

-- Change the name of the role
Alter Role TestRole With Name = NewRole;

-- Check to see if alter role statements are in the log
Select SUSER_SNAME([Transaction SID]) As Changer,
    [Transaction Name],
    [Begin Time]
From fn_dblog(null, null)
Where [Transaction Name] In ('CREATE USER', 'ADD/DROP ROLE MEMBER', 'GRANT', 'ALTER ROLE');

-- Back up the log
Backup Log TestAlterRole To Disk = 'c:\bak\TestAlterRole_2.trn' With Init;

-- Is it in the log backup now?
Select SUSER_SNAME([Transaction SID]) As Changer,
    [Transaction Name],
    [Begin Time]
From fn_dump_dblog(null, null, N'Disk', 1, N'c:\bak\TestAlterRole_2.trn',
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null)
Where [Transaction Name] In ('CREATE USER', 'ADD/DROP ROLE MEMBER', 'GRANT', 'ALTER ROLE');

-- Cleanup
Use TestAlterRole;
Alter Database TestAlterRole Set Single_User With Rollback Immediate;
Use master;
Drop Database TestAlterRole;

/* Run the below only if you want to disable containment
If Not Exists (Select * From sys.databases
            Where Containment > 0)
  Begin
    Exec sp_configure 'contained database authentication', 0;
    Reconfigure;
  End
Else
  Begin
    RaisError('Contained databases found on server. Containment not disabled.', 16, 1);
  End
*/