CV lietotāju tiesību noskaidrošanai

Rakstā parādīta Catalog View skata sys.database_permissions lietošana. Izveidots skripts, ar kura palīdzību var iegūt datu bāzes objektus, lietotājus un lietotāju grupas, kam piešķirtas tiesības uz šiem objektiem. Papildus tam, lasāmāku vaicājumu rezultātu iegūšanai, izmantota Pivot klauza.

Lai iegūtu datu bāzes objektus un tiem piešķirtās tiesības, var izmantot šādu SQL vaicājumu:

Select object_name(major_id) objekts, user_name(grantee_principal_id) lietotajs_vai_loma, permission_name, state_desc, *
From sys.database_permissions sp
Where class_desc = 'OBJECT_OR_COLUMN'

Kā redzams- vaicājumā izmantotas divas SQL Server iebūvētās funkcijas. Jāpievērš uzmanība kolonnai "state_desc"- tā ir nepieciešama, jo tiesības var ne tikai speciāli piešķirt, bet arī, piemēram, aizliegt.

Un tad mazliet sarežģītāka konstrukcija izmantojot Pivot klauzu, ar mērķi padarīt vaicājuma rezultātus lasāmākus:

Select object_name(major_id) objekts, user_name(grantee_principal_id) lietotajs_vai_loma,
state_desc,
    Case When [SL] > 0 Then 'X' Else '' End [Select], 
    Case When [IN] > 0 Then 'X' Else '' End [Insert], 
    Case When [UP] > 0 Then 'X' Else '' End [Update], 
    Case When [DL] > 0 Then 'X' Else '' End [Delete],
    Case When [AL] > 0 Then 'X' Else '' End [ALTER],
    Case When [EX] > 0 Then 'X' Else '' End [Execute]
From 
    (
        Select major_id, grantee_principal_id, state_desc, [Type] 
        From sys.database_permissions 
        Where class_desc = 'OBJECT_OR_COLUMN'
    ) sp 
    Pivot
    (
        Count([Type])
        For [Type] in ([IN], [SL], [UP], [DL], [AL], [EX])
    ) As PivotTable
Order By object_name(major_id), user_name(grantee_principal_id)

Vaicājums gan nav universāls- tas parāda tikai Select, Insert, Delete, Update un Execute tiesības. Vaicājuma rezultāti, manuprāt, viegli saprotami. Izskatās apmēram tā (šo to gan aizkrāsoju):
 
[Papildināts 2015-07-09]
Cenšoties iegūt pēc iespējas pilnīgāku priekšstatu, par tiesībām, kas piešķirtas datu bāzes lietotājiem un lomām! Joprojām esmu meklējumos, kā to vispārskatāmāk izdarīt.
Zemāk papildināts vaicājums, ar kura palīdzību tiek parādītas tiesības uz datu bāzes objektiem (sakārtotas pēc lietotāja/lomas). Jāņem vērā, ka neesmu apstrādājis visus datu bāzes objektus uz kuriem var piešķirt tiesības, bet tikai tos, kas bija sastopami man interesējošajās datu bāzēs (tātad, visdrīzāk kaut kad būs vēl papildinājumi):

Select 
sp.grantee_principal_id,
    user_name(sp.grantee_principal_id) grantee, 
    user_name(sp.grantor_principal_id) grantor, 
    case sp.class 
        when 1 then o.type_desc
        when 17 then 'SERVICE'
        when 3 then 'SCHEMA'
        when 25 then 'CERTIFICATE'
        when 24 then 'SYMMETRIC_KEYS'
        when 0 then 'DATABASE'
when 4 then 'DATABASE PRINCIPAL'
        else NULL
    end class,
    case sp.class 
            when 1 then object_schema_name(sp.major_id) + '.' + o.name
            when 17 then s.name 
            when 3 then sch.name Collate Latvian_CI_AS -- sys.services atšķiras kolācija
            when 25 then c.name
            when 24 then sk.name
            when 1 then 'DATABASE'
    when 4 then pr.name
            else NULL
        end object_name,  
    sp.state_desc,
    sp.permission_name
From sys.database_permissions sp
    Left Join sys.all_objects o on sp.major_id = o.object_id and sp.class = 1
    Left Join sys.services s on sp.major_id = s.service_id and sp.class = 17
    Left Join sys.schemas sch on sp.major_id = sch.schema_id and sp.class = 3 
    Left Join sys.certificates c on sp.major_id = c.certificate_id and sp.class = 25
    Left Join sys.symmetric_keys sk on sp.major_id = sk.symmetric_key_id and sp.class = 24
    Left Join sys.database_principals pr on sp.major_id = pr.principal_id and sp.class = 4
Order By sp.grantee_principal_id, sp.class, case when sp.class = 1 then o.type_desc else '' end

[Papildināts 2012-06-15]
Lietotāju piederība lomām (kā arī lomu piederība citām lomām) ir apskatāma izmantojot zemāk esošo SQL vaicājumu (bez garākiem paskaidrojumiem gan- principā kad būs laiks, pamēģināšu izveidot pārskatu līdzīgi kā ar SSMS: Datu bāzes tabulu pārskats):

Select
    DB_NAME() database_name,
    dpr.type_desc,
    USER_NAME(drm.member_principal_id) member_principal,
    dpr.is_fixed_role,
    dpr.default_schema_name,
    dpr.create_date,
    dpr.modify_date,
    dp.UserPermissions, -- skaits, cik tiesību piešķirts lomai/lietotājam
    USER_NAME(drm.role_principal_id) role_principal,
    dpr2.type_desc,
    dpr2.is_fixed_role,
    dpr2.default_schema_name,
    drm.member_principal_id,
    drm.role_principal_id
From sys.database_role_members drm
    left join sys.database_principals dpr on drm.member_principal_id = dpr.principal_id
    left join sys.database_principals dpr2 on drm.role_principal_id = dpr2.principal_id
    left join
        (
            Select grantee_principal_id, Count(1) UserPermissions
            From sys.database_permissions
            Where NOT (class = 0 AND [type] = 'CO') -- connect tiesības nogriežu
            Group By grantee_principal_id
        ) dp on drm.member_principal_id = dp.grantee_principal_id
Order By dpr.type_desc, member_principal_id