Как проверить принадлежность пользователя SQL Server к группе или к роли

Опытные ‎разработчики‏ ‎и ‎администраторы ‎прекрасно ‎знают, ‎что‏ ‎никогда ‎и‏ ‎ни‏ ‎при ‎каких ‎обстоятельствах‏ ‎нельзя ‎назначать‏ ‎разрешения ‎отдельным ‎пользователям. ‎Только‏ ‎группам‏ ‎или ‎ролям!

Это‏ ‎действительно ‎правильный‏ ‎подход, ‎но ‎если ‎вы ‎его‏ ‎исповедуете,‏ ‎время ‎от‏ ‎времени ‎у‏ ‎вас ‎будет ‎возникать ‎необходимость ‎проверять‏ ‎членство‏ ‎конкретного‏ ‎пользователя ‎(как‏ ‎правило, ‎текущего)‏ ‎к ‎той‏ ‎или‏ ‎иной ‎группе‏ ‎или ‎его ‎принадлежность ‎к ‎конкретной‏ ‎роли.

В ‎SQL-сервере‏ ‎есть‏ ‎простые ‎функции ‎и‏ ‎представления ‎для‏ ‎таких ‎проверок.

Для ‎демонстрации ‎этих‏ ‎механизмов‏ ‎я ‎создам‏ ‎несколько ‎ролей‏ ‎и ‎групп ‎разных ‎типов. ‎Разумеется,‏ ‎проверим,‏ ‎учитывает ‎ли‏ ‎проверка ‎вложенность.‏ ‎В ‎моих ‎примерах ‎проверяемый ‎пользователь‏ ‎входит‏ ‎только‏ ‎в ‎самую‏ ‎глубоко ‎вложенную‏ ‎группу/роль ‎—‏ ‎№‏ ‎3 ‎(помимо‏ ‎членства ‎в ‎одной-двух ‎стандартных).

Роли ‎базы‏ ‎данных

Анализируя ‎системные‏ ‎представления‏ ‎Sys.Database_Principals и ‎Sys.Database_Role_Members, вы ‎получите‏ ‎полную ‎картину‏ ‎о ‎членстве ‎в ‎группах.

Однако,‏ ‎как‏ ‎правило, ‎полная‏ ‎картина ‎нам‏ ‎не ‎требуется. ‎Достаточно ‎просто ‎проверить‏ ‎принадлежность‏ ‎текущего ‎пользователя‏ ‎к ‎одной‏ ‎конкретной ‎роли. ‎Для ‎такой ‎задачи‏ ‎имеется‏ ‎более‏ ‎простая ‎функция‏ ‎Is_Member. Удобно, ‎что‏ ‎она ‎сама‏ ‎умеет‏ ‎работать ‎с‏ ‎вложенными ‎ролями.

У ‎этой ‎функции ‎есть‏ ‎расширенная ‎версия‏ ‎—‏ ‎Is_RoleMember, у ‎которой ‎есть‏ ‎второй ‎параметр,‏ ‎через ‎который ‎можно ‎указать‏ ‎идентификатор‏ ‎пользователя ‎(database_principal),‏ ‎членство ‎которого‏ ‎хотим ‎проверить.

Группы ‎Active ‎Directory

Функция ‎Is_Member умеет‏ ‎также‏ ‎работать ‎с‏ ‎группами ‎Active‏ ‎Directory. ‎И ‎тоже ‎с ‎учётом‏ ‎вложенности.

Учтите,‏ ‎что‏ ‎эта ‎функция‏ ‎ориентируется ‎на‏ ‎SID’ы ‎групп,‏ ‎рассчитанные‏ ‎при ‎входе‏ ‎пользователя. ‎Поэтому, ‎если ‎пользователь ‎был‏ ‎включён ‎в‏ ‎группу‏ ‎после ‎соединения ‎с‏ ‎сервером, ‎придётся‏ ‎переподключиться.

Серверные ‎роли

Вряд ‎ли ‎вам‏ ‎когда-нибудь‏ ‎на ‎практике‏ ‎придётся ‎программно‏ ‎проверять ‎членство ‎в ‎серверной ‎роли,‏ ‎однако‏ ‎такая ‎возможность‏ ‎имеется ‎—‏ ‎представление ‎Sys.Server_Role_Members.

А ‎также ‎функция ‎Is_SrvRoleMember, которая‏ ‎работает‏ ‎аналогично‏ ‎Is_RoleMember, но ‎на‏ ‎уровне ‎контекста‏ ‎сервера ‎(login),‏ ‎а‏ ‎не ‎базы‏ ‎данных.