Solved

Roles in MS SQL Server

Posted on 2002-07-04
3
648 Views
Last Modified: 2007-12-19
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!
0
Comment
Question by:nef112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:curtis591
ID: 7130125
I have assigned security at the column level but this is what I use for table level.  I am guessing if I used column level it would still work.

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

0
 

Author Comment

by:nef112
ID: 7130181
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!
0
 
LVL 6

Accepted Solution

by:
curtis591 earned 50 total points
ID: 7130202
I just tried that query and it should be returning other sql logins in the rolename column with which rights they have been explictly given to tables.  I am using a sql 2000 box.


SELECT     sysusers_name.name as user_name, sysusers_group.name AS group_name, sysusers_group.uid as group_id
FROM         dbo.sysusers sysusers_name INNER JOIN
                      dbo.sysmembers ON sysusers_name.uid = dbo.sysmembers.memberuid INNER JOIN
                      dbo.sysusers sysusers_group ON dbo.sysmembers.groupuid = sysusers_group.uid

will return the users of a role so I would think if you combine these queries you should be able make a stored procedure that takes a user name and retrieves the rights for the roles he is in and tables he has access to.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question