I used to following to create a role in Azure SQL database, grant permissions to the role and assigned the role to the user:
CREATE ROLE [DepartmentReadOnly] AUTHORIZATION [dbo]
GRANT SELECT ON tblDepartment TO DepartmentReadOnly
CREATE USER [user1] FROM LOGIN [user1];
EXEC sp_addrolemember DepartmentReadOnly, user1;
Now what sql do I use to retrieve the DepartmentReadOnly role name and permissions from the database as well as user1 user information including which roles user1 belongs to?
There is no sys.server_principals system view showing up in Azure so I am lost. Can anyone please help?
Best How To :
Here is what worked:
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