SHARE
TWEET

Untitled

SQLSoldier Jun 24th, 2015 248 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Make sure containment is enabled for this test
  2. Exec sp_configure 'contained database authentication', 1;
  3. Reconfigure;
  4. Go
  5.  
  6. Create Database TestAlterRole Containment = Partial;
  7. Go
  8.  
  9. -- Make sure in full recovery model
  10. Alter Database TestAlterRole Set Recovery Full;
  11. Go
  12.  
  13. -- Make sure full recovery initialized
  14. Backup Database TestAlterRole To Disk = 'c:\bak\TestAlterRole.bak' With Init;
  15. Backup Log TestAlterRole To Disk = 'c:\bak\TestAlterRole_1.trn' With Init;
  16. Go
  17.  
  18. -- Create a Role and User
  19. Use TestAlterRole;
  20.  
  21. Create Role TestRole;
  22. Create User TestUser With Password = 'Not$trongEnuff'
  23. Go
  24.  
  25. -- Add the user to the role
  26. Alter Role TestRole Add Member TestUser;
  27.  
  28. -- Change the name of the role
  29. Alter Role TestRole With Name = NewRole;
  30.  
  31. -- Check to see if alter role statements are in the log
  32. Select SUSER_SNAME([Transaction SID]) As Changer,
  33.         [Transaction Name],
  34.         [Begin Time]
  35. From fn_dblog(null, null)
  36. Where [Transaction Name] In ('CREATE USER', 'ADD/DROP ROLE MEMBER', 'GRANT', 'ALTER ROLE');
  37.  
  38. -- Back up the log
  39. Backup Log TestAlterRole To Disk = 'c:\bak\TestAlterRole_2.trn' With Init;
  40.  
  41. -- Is it in the log backup now?
  42. Select SUSER_SNAME([Transaction SID]) As Changer,
  43.         [Transaction Name],
  44.         [Begin Time]
  45. From fn_dump_dblog(null, null, N'Disk', 1, N'c:\bak\TestAlterRole_2.trn',
  46.         null, null, null, null, null, null, null, null, null, null, null,
  47.         null, null, null, null, null, null, null, null, null, null, null,
  48.         null, null, null, null, null, null, null, null, null, null, null,
  49.         null, null, null, null, null, null, null, null, null, null, null,
  50.         null, null, null, null, null, null, null, null, null, null, null,
  51.         null, null, null, null, null, null, null, null)
  52. Where [Transaction Name] In ('CREATE USER', 'ADD/DROP ROLE MEMBER', 'GRANT', 'ALTER ROLE');
  53.  
  54. -- Cleanup
  55. Use TestAlterRole;
  56. Alter Database TestAlterRole Set Single_User With Rollback Immediate;
  57. Use master;
  58. Drop Database TestAlterRole;
  59.  
  60. /* Run the below only if you want to disable containment
  61. If Not Exists (Select * From sys.databases
  62.                         Where Containment > 0)
  63.   Begin
  64.         Exec sp_configure 'contained database authentication', 0;
  65.         Reconfigure;
  66.   End
  67. Else
  68.   Begin
  69.         RaisError('Contained databases found on server. Containment not disabled.', 16, 1);
  70.   End
  71. */
RAW Paste Data
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top