Transact SQL и MS SQL Server - получаем список ролей для текущего пользователя и всех пользователей

Механизм ролей в MS SQL Server не так прост, как кажется, если имя пользователя добывается легко и непринужденно:

SELECT SYSTEM_USER

то список ролей можно получить с помощью утюга, паяльника, дыбы, испанского сапога и следующего запроса

--- РОЛИ ДЛЯ ТЕКУЩЕГО ПОЛЬЗОВАТЕЛЯ
WITH CTE_Roles (role_principal_id)
AS
(
SELECT role_principal_id 
FROM sys.database_role_members
WHERE member_principal_id = USER_ID()
UNION ALL
SELECT drm.role_principal_id
FROM sys.database_role_members drm
  INNER JOIN CTE_Roles CR
    ON drm.member_principal_id = CR.role_principal_id
)
SELECT USER_NAME(role_principal_id) RoleName
FROM CTE_Roles
UNION ALL
SELECT 'public'
ORDER BY RoleName;

Типичный программист любопытен, хочет все знать и не ищет простых путей - подробную информацию о всех пользователях базы данных он выведет следующим запросом

------------ ПОДРОБНАЯ ИНФОРМАЦИЯ О ПОЛЬЗОВАТЕЛЯХ БАЗЫ ДАННЫХ
select
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on sp.sid=dbp.sid join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id

Комментарии

Transact SQL и MS SQL Server - получаем список ролей для текущего пользователя и всех пользователей — Комментарии (5)

  1. На MSSQL 2000 не работает. Только на 2005.
    Прекрасные статьи, спасибо за проделанную работу

  2. Well it was a good laugh. I think if you want to help Bette fight cancer, you should avoid this and go to the Chdn#rel&i8217;s health fund benefit. It’s too pricey for me, but she’s performing.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *


*

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>