Solved

Roles in MS SQL Server

Posted on 2002-07-04
3
637 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

14 Experts available now in Live!

Get 1:1 Help Now