anyone know how to query system tables to retrieve a list of users that are permissioned for a stored procedure?
and vice versa... a list of stored procedure a user has permission to execute?
i searched for it online and came up with nothing... can someone please help. thanks, all.
Below is a statement to get somewhat of an idea of what you are looking for. When the user is setup to have databse level access it does not list the procedures out one by one of what they have access to, because they have access to everything. However if the user is granted access only to specific objects or columns it tells you what they do have permissions to.
select class_desc,prin.[name],obj.[name]
from sys.database_permissions p
left join sys.database_principals prin
on p.grantee_principal_id = prin.principal_id
left join sys.objects obj
on p.major_id = obj.object_id
If this doesn't totally answer your question at least it might help you get an idea of how you can get the information you are looking for.
Let me know if I can be of any further assistance.
No comments:
Post a Comment