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...

How to grant a user read-only rights to a schema in Oracle 11g

There are two ways to grant a user read-only privileges on a single schema in Oracle:

1) Retrieve all the objects and grant SELECT privileges on each object to the user in question

2) Create a role and to that role grant SELECT privileges on each object in the schema and then grant that role to the user.

Script below can spool all the necessary GRANT statements to a file, given username of the user (or role) to grant priileges to and given the nschema name:

set pages 0;
set linesize 100;
set feedback off; 
set verify off; 

spool C:\Test\GET_ALL_SCHEMA_OBJECTS.sql

SELECT 'GRANT SELECT ON ' || table_name || ' TO &&new_user;' FROM dba_tables WHERE owner=upper('&&schema_name');
 
SELECT 'GRANT SELECT ON ' || view_name || ' TO &&new_user;' FROM dba_views WHERE owner=upper('&&schema_name');
 
set serveroutput off;
spool off;

How to retrieve all permissions granted to a particular user

Below is the query that will give you all the permissions granted to a particular user for the current database:

SELECT class_desc [Permission Level], CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name [Permission]
  , state_desc
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id)='user1'