January 5, 2010 at 7:20 am
Is there a way to link sys.database_principals to sys.objects (or something) to query out what objects a database user has permissions to?
The Redneck DBA
January 5, 2010 at 7:53 am
Yes. Take a look at sys.database_permissions.
K. Brian Kelley
@kbriankelley
January 5, 2010 at 10:22 am
Can you please give the example or tell us what we should be JOINing on?
January 5, 2010 at 10:27 am
I wound up with this:
select *
from
sys.database_permissions p
inner join sys.objects o
on p.major_id = o.object_id
inner join sys.schemas s
on o.schema_id = s.schema_id
where
p.class = 1
and p.major_id > 0
and p.grantee_principal_id = 22
and o.type = 'P'
The Redneck DBA
January 5, 2010 at 11:11 am
INNER JOIN is going to filter out data. This should do it:
Quick 2005/2008 Script to Export Permissions
K. Brian Kelley
@kbriankelley
January 5, 2010 at 12:08 pm
What do you mean by it will filter things out? Won't it just return a set of SPs that user has permission to execute?
The Redneck DBA
January 5, 2010 at 12:29 pm
Keep in mind that with securables, permissions are inherited. So if I grant EXECUTE at the schema level, then the user has EXECUTE rights on all stored procedures within that schema. Yes, technically that means if someone grants SELECT at the database level, the user has SELECT against all tables and views in the database. So my point is by just doing the inner joins like you're doing, you only get the permissions against the objects like tables, views, etc. You don't capture permissions against the schema.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply