Как узнать относится ли пользователь к определенной группе или роли в MS SQL Server на Transact-SQL?

Если у Вас встала задача проверить, входит ли текущий пользователь в определенную группу или роль базы данных Microsoft SQL Server, а также является ли данное имя входа членом указанной предопределенной роли сервера, то данный материал в этом Вам поможет, так как в нем я расскажу и покажу, каким образом все это можно делать на Transact-SQL.

Как Вы, наверное, знаете одним из правильных подходов к построению системы безопасности в Microsoft SQL Server, является то, что назначать права (разрешения) необходимо только группам или ролям, конкретным пользователям назначать права можно только в исключительных случаях, такой подход значительно упростит управление разрешениями на сервере.

Необходимость определения принадлежности пользователя к группе или роли базы данных может возникнуть, например, тогда, когда Вам в процедуре перед выполнением какого-нибудь действия нужно проверить является ли пользователь, который запустил эту процедуру, членом той или иной группы базы данных или членом предопределенной роли сервера, например, для того чтобы быть точно уверенным, что пользователь имеет необходимые права для выполнения текущей операции.

Функционал для проверки принадлежности пользователя к определённой группе, роли базы данных, или к роли сервера в Microsoft SQL Server, конечно же, есть и сейчас мы его рассмотрим.

Примечание! В качестве сервера у меня выступает Microsoft SQL Server 2016 Express. В предыдущих версиях представленные ниже функции также работают.

Функция IS_MEMBER в T-SQL

IS_MEMBER – это встроенная функция в языке T-SQL, она показывает является ли текущий пользователь членом указанной группы или роли базы данных.

Принимает один параметр, им может быть «Имя проверяемой группы» или «Имя проверяемой роли SQL Server». Передавать название серверной роли в IS_MEMBER нельзя, для этого существует другая функция.

Возвращает следующие значение:

• 0 - Текущий пользователь не является членом указанной группы или роли;
• 1 - Текущий пользователь является членом указанной группы или роли;
• NULL – Указана недопустимая группа или роль.

В случае если пользователь был добавлен или удален из определенной группы, в то время, когда он был подключен к экземпляру SQL Server, функция IS_MEMBER отобразит изменения только после переподключения пользователя.

Пример работы функции IS_MEMBER

Давайте для примера определим является пользователь, под которым мы работаем, членом группы db_owner, для этого напишем вот такой запрос

	
        IF IS_MEMBER ('db_owner') = 1
		SELECT 'Пользователь является членом группы db_owner' AS [Результат]
	ELSE
		SELECT 'Пользователь НЕ входит в группу db_owner' AS [Результат]

Как видно я являюсь членом группы db_owner.

Функция IS_SRVROLEMEMBER в T-SQL

IS_SRVROLEMEMBER – это встроенная функция в языке T-SQL, которая проверяет является ли данное имя входа членом указанной предопределенной роли сервера. Именно этой функцией необходимо проверять членство в серверных ролях.

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

Функция IS_SRVROLEMEMBER возвращает следующие значения:

• 0 – Имя входа не является членом указанной роли;
• 1 – Имя входа является членом указанной роли;
• NULL – Указано недопустимое значение роли или имени входа.

Пример работы функции IS_SRVROLEMEMBER

Сейчас давайте узнаем, является ли текущее имя входа членом серверной роли sysadmin.

	
        IF IS_SRVROLEMEMBER ('sysadmin') = 1
		SELECT 'Пользователь является членом роли сервера sysadmin' AS [Результат]
	ELSE
		SELECT 'Пользователь НЕ является членом роли сервера sysadmin' AS [Результат]

В данном случае я также являюсь членом роли сервера sysadmin.

На этом у меня все, пока!