Работа с пользователями MSSQL в Azure SQL

Важные моменты в работе с пользователями MSSQL через Microsoft SQL Server Management Studio в Azure Cloud.

0) Microsoft SQL Server Management Studio не позволяет создавать и редактировать пользователей через UI для MSSQL базы в Azure Cloud

Microsoft SQL Server Management Studio не позволяет создавать и редактировать пользователей через UI если база находится в в Azure Cloud. Это можно сделать только через Transact-SQL запросы. (подробнее https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/)

1) Для создания запроса к конкретной базе требуется перейти в раздел Database, далее правым кликом на базу данных, New Query.

2) Как создать пользователя в нужной БД? Для подключения к SQL создается Login, для доступа к конкретной базе надо создать User и связать его с логином.

• Security->Logins
• правым кликом, New login

• В нужной БД раскрыть раздел Security->Users
• правым кликом New user, (строку с WITH DEFAULT_SCHEMA можно удалить, подробнее о схемах https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=azure-sqldw-latest)

2) При поиске в интернете можно найти решения для старых версий SQL Server 2000. Старые системные таблицы могли быть удалены и замены на view. Соответствие старых таблиц и новых view можно найти здесь https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql?view=sql-server-2017

3) Как посмотреть список ролей и доступов пользователей?

• Создаем запрос для конкретной БД (см. п.1)
• Выполняем запрос для просмотра ролей

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 DISTINCT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

Подсмотрено тут: https://stackoverflow.com/questions/31120912/how-to-view-the-roles-and-permissions-granted-to-any-database-user-in-azure-sql

4) Как дать роль или забрать роль?

• Назначить роль для чтения

ALTER ROLE db_datareader ADD MEMBER user1;

• Убрать роль для чтения

ALTER ROLE db_datareader DROP MEMBER user1;

Подсмотрено тут: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver15#examples