Can't view Proporties of Users, Roles and Schema on Azure Sql Server

Question

I have an Azure Sql Server where i want to change some Users, Roles and Schemas. This works fine with Sql Querys but i can not open the "Properties" Window.

But on my On Premise and Local Sql Server, i can view it with no Problems

Where can i enable the Properties Window on Azure?

I have tried ssms 2012, 2014, 2016 & 2017

Answer 1

In Azure at the moment, you can only change these properties with a sql script. Very annoying and unnecessarily complicated. The same goes to viewing the permissions once they are set - SQL only.

I have found the following SQL scripts helpful in checking permissions that are set

SELECT DISTINCT pr.principal_id, pr.name AS [UserName], 
    pr.type_desc AS [User_or_Role], 
    pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
    pe.permission_name, pe.class_desc, o.[name] AS 'Object' 
FROM sys.database_principals AS pr 
JOIN sys.database_permissions AS pe 
    ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)

select m.name as Member, r.name as Role
from sys.database_role_members
inner join sys.database_principals m on 
    sys.database_role_members.member_principal_id = m.principal_id
inner join sys.database_principals r on 
    sys.database_role_members.role_principal_id = r.principal_id


SELECT DP1.name AS DatabaseRoleName,   
isnull (DP2.name, 'No members') AS DatabaseUserName   
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
    ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
    ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name; 


SELECT p.name, prm.permission_name, prm.class_desc,
    prm.state_desc, p2.name as 'Database role',
    p3.name as 'Additional database role'
FROM sys.database_principals p
JOIN sys.database_permissions prm
   ON p.principal_id = prm.grantee_principal_id
LEFT JOIN sys.database_principals p2
   ON prm.major_id = p2.principal_id
LEFT JOIN sys.database_role_members r
   ON p.principal_id = r.member_principal_id
LEFT JOIN sys.database_principals p3
   ON r.role_principal_id = p3.principal_id
WHERE p.name <> 'public'