MSSQL Hack: be able to execute sys.sp_addrolemember within a transaction

sys.sp_addrolemember is a MS SQL Server system stored procedure that cannot be executed within a user-defined transaction.
The work around is to terminate/commit any outer transaction (only in scenarios where the caller does not have any change *prior to calling sp_addrolemember*, that may need rolling back if something fails *after calling sp_addrolemember*, as this might affect the caller to lose the capability of rolling back unwanted changes).

SET IMPLICIT_TRANSACTIONS OFF -- make sure server does not start any implicit transaction 
WHILE XACT_STATE() = 1 -- the transaction is committable
  COMMIT TRAN -- Try committing any outer transaction, in order for 

EXEC sp_addrolemember
  @rolename = 'rolename',
  @membername = 'db_user_name'

Comparing between different collations

In MSSQL 2005, when comparing string (nvarchar, varchar, etc..) between different collations e.g.
SELECT * FROM sys.sql_logins
WHERE NAME NOT IN (
SELECT login_name FROM RESOURCE
)

SQL Server would throw an error due to different collations set for the columns compared:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

So, to be able to compare between different collations, COLLATEE DATABASE_DEFAULT can be used, which would convert the column(s) to the collation the current database is using, and the new query looks like:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN (
SELECT login_name COLLATE DATABASE_DEFAULT FROM RESOURCE
)

In addition, if the collation for one of the columns being compared is known, all the other columns can be converted to the same collation:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN (
SELECT login_name FROM RESOURCE
)