Как проверить принадлежность пользователя 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), а не базы данных.