Security granted to Administrative Users

-- All permissions to objects

with [SecuredObjects] as (

select CategoryID [ID], CategoryName Name, 's' as [type] from RBAC_Categories

union all

select SiteID [ID], CollectionName Name , 'c' as [type] from v_Collections

)

select LogonName, RoleName,

stuff((SELECT distinct ', ' + cast(so.Name as varchar(50)) + ' (' + cast(CategoryID as varchar(10)) +')'

FROM vRBAC_Permissions t2 inner join SecuredObjects so on so.ID = t2.CategoryID and so.[type] = 's'

where t2.RoleName = t1.RoleName and t2.LogonName = t1.LogonName and CategoryTypeID = 29

FOR XML PATH('')),1,1,'') as [Scopes],

stuff((SELECT distinct ', ' + cast(so.Name as varchar(50)) + ' (' + cast(CategoryID as varchar(10)) +')'

FROM vRBAC_Permissions t2 inner join SecuredObjects so on so.ID = t2.CategoryID and so.[type] = 'c'

where t2.RoleName = t1.RoleName and t2.LogonName = t1.LogonName and CategoryTypeID = 1

FOR XML PATH('')),1,1,'') as [Collections]

from vRBAC_Permissions t1

--where LogonName = 'ZZZ\_cmoorehead'

group by LogonName, RoleName

order by LogonName, RoleName
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s