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