Saturday, April 17, 2010

Determining a user's permissions on sql objects

I use a variant of this for auditing user permissions for security checks. My preference is a user should only have execute access to a stored procedure and thats it. This will find tables the user has select access on (to return all permissions simply remove the: where permission_name='select' and subentity_name ='' )

This will work for users who receive permissions as part of role membership as well as direct permissions on an object.


--this WILL work for role based permissions as well.
select b.name + '.' + a.name, permissions.permission_name from sys.tables a
inner join sys.schemas b on a.schema_id=b.schema_id
cross apply (select * From fn_my_permissions(b.name + '.' + a.name, 'object') where permission_name='select' and subentity_name ='') as permissions
where a.type='u'

--should only have execute
select b.name + '.' + a.name, permissions.permission_name from sys.procedures a
inner join sys.schemas b on a.schema_id=b.schema_id
cross apply (select * From fn_my_permissions(b.name + '.' + a.name, 'object') ) as permissions

No comments:

Post a Comment