Escaping underscore or other special characters in SQL query WHERE clause

There are two ways to escape underscore in SQL Server: square brackets and specifying escape character

SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%\_%' ESCAPE '\'

or

SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%[_]%' 

Retrieving all the user created users, roles, and associated permissions in Azure SQL

I have been struggling every time I needed to retrieve all the users, roles, and the associated permisisons that I have created since Azure SQL is a little different from SQL Server I am used to when it comes to user administration. So I came up with this query that I'd like to share:

SELECT p.[name] as 'Principal_Name',
   CASE WHEN p.[type_desc]='SQL_USER' THEN 'User'
   WHEN p.[type_desc]='DATABASE_ROLE' THEN 'Role' END As 'Principal_Type',
   --principals2.[name] as 'Grantor',
   dbpermissions.[state_desc] As 'Permission_Type',
   dbpermissions.[permission_name] As 'Permission',
   CASE WHEN so.[type_desc]='USER_TABLE' THEN 'Table'
   WHEN so.[type_desc]='SQL_STORED_PROCEDURE' THEN 'Stored Proc'
   WHEN so.[type_desc]='VIEW' THEN 'View' END as 'Object_Type',
   so.[Name] as 'Object_Name'
   FROM [sys].[database_permissions] dbpermissions
   LEFT JOIN [sys].[objects] so ON dbpermissions.[major_id] = so.[object_id] 
   LEFT JOIN [sys].[database_principals] p ON dbpermissions.  [grantee_principal_id] = p.[principal_id]
   LEFT JOIN [sys].[database_principals] principals2  ON dbpermissions.[grantor_principal_id] = principals2.[principal_id]
   WHERE p.principal_id > 4

Adding principal_id > 4 ensures removal of dbo, public etc...