Hi,
How can we determine permissions of a database role?
I could only find out how to determine user and login perms:
EXECUTE AS user = 'Omid' GO SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY') GO REVERT GOAny suggestions?I can't believe there is no reply after a day in MSDN forums. Anyway not to disappoint folks with the same problem, there is a very very stupid solution: Create a temp user(login) and add this user to the database role and then check the permissions and at last remove the user!|||Hey, sometimes we all have to take a break
Are you using 2005? If so, you can use the sys.database_permissions view. Here is a blog that I forgot that I wrote about this until I started doing some research for you
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1485.entry
This query gets table permissions in a database, with object names... Easy enough to expand for other types of objects:
select database_permissions.permission_name,
coalesce(objects.type_desc,database_permissions.class_desc)
+ case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
else '' end as object_type,
case database_permissions.class_desc
when 'SCHEMA' then schema_name(major_id)
when 'OBJECT_OR_COLUMN' then
case when minor_id = 0 then object_name(major_id)
else (select object_name(object_id) + '.'+ name
from sys.columns
where object_id = database_permissions.major_id
and column_id = database_permissions.minor_id) end
else 'other' end as object_name,
database_principals.name as database_principal,
database_permissions.state_desc as grant_state
from sys.database_permissions
join sys.database_principals
on database_permissions.grantee_principal_id = database_principals.principal_id
left join sys.objects --left because it is possible that it is a schema
on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name
I really appreciate it. Although I've already implemented the "stupid" solution, I'll surely change it to use the query as soon as possible.
Thanks again.
