nef112
asked on
Roles in MS SQL Server
Is there a way to, once you know a user's role(s), find out what tables and columns the user is allowed to view via a stored procedure?
The procedure would have to find all columns and tables the user could view for all roles he is a member of.
Thanks for your help!
The procedure would have to find all columns and tables the user could view for all roles he is a member of.
Thanks for your help!
ASKER
This works for tables and columns associated with the user's role, but I also need to know if the user has access to any tables/columns outside of the role to which he belongs. Sorry, I should've been clearer in the question to start with.
Thanks again!
Thanks again!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select sysusers.name as role_name, sysobjects.name as object_name, sysusers.uid as group_id,
max(case when sysprotects.action = 193 then 'Yes' else 'No' end) as select_rights,
max(case when sysprotects.action = 195 then 'Yes' else 'No' end) as insert_rights,
max(case when sysprotects.action = 196 then 'Yes' else 'No' end) as delete_rights,
max(case when sysprotects.action = 197 then 'Yes' else 'No' end) as update_rights
from sysusers
left outer join syspermissions on syspermissions.grantee = sysusers.uid
left outer join sysobjects on syspermissions.id = sysobjects.id
left outer join sysprotects on sysobjects.id = sysprotects.id and sysprotects.uid = sysusers.uid
group by sysusers.name, sysobjects.name, sysusers.uid