Retrieving user created roles and user info in Azure

Question

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]
GO

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?

score 1 · Answer 1 · answered Jun 05 '15 at 19:28

1

"select * from sys.sql_logins" used as sys.server_principals in azure, and you must run query in master db

answered Jun 05 '15 at 19:28

Danila Polevshchikov

2,228
2
24
35

sys.sql_logins only gives me the logins, not roles. – Coding Duchess Jun 05 '15 at 19:50

tmullaney · Answer 2 · 2015-06-11T16:52:32.310

0

It sounds like you are looking for these catalog views in your user database:

sys.database_principals has the role (see the bottom of the page for an example to list all permissions by joining with sys.database_permissions).
sys.database_role_members has the role memberships.

edited Jun 11 '15 at 16:52

answered Jun 06 '15 at 04:47

tmullaney

1,625
9
10

As tmullaney states, what you want is [sys.database_principals](https://msdn.microsoft.com/en-us/library/ms187328.aspx), [sys.database_role_members](https://msdn.microsoft.com/en-us/library/ms189780.aspx), and [sys.database_permissions](https://msdn.microsoft.com/en-us/library/ms188367.aspx). Permissions are recorded in sys.database_permissions. `select * from sys.database_principals select * from sys.database_role_members select * from sys.database_permissions` – Jack Richins Jun 06 '15 at 07:03
I selected name from sys.database_principals and DepartmentReadOnly role is not there... – Coding Duchess Jun 08 '15 at 14:33
OK, I found the role name when I ran select * from sys.database_principals on my dastabase and not on master database. But how do I retrieve group members for my group? the usernames are not in database_principals table – Coding Duchess Jun 08 '15 at 14:41
sys.database_role_members contains the principal_id for both the role and the member user, so you can join with sys.database_principals to get both the role names and the user names. – tmullaney Jun 11 '15 at 16:48
Ex: `select dp_user.name as username, dp_role.name as role from sys.database_role_members drm inner join sys.database_principals dp_user on dp_user.principal_id = drm.member_principal_id inner join sys.database_principals dp_role on dp_role.principal_id = drm.role_principal_id` – tmullaney Jun 11 '15 at 16:49

score 0 · Accepted Answer · answered Jun 18 '15 at 15:54

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

http://elena-sqldba.blogspot.com/2015/06/retrieving-all-user-created-users-roles.html

Retrieving user created roles and user info in Azure

3 Answers3