Solved

Roles in MS SQL Server

Posted on 2002-07-04
3
641 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now