[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Roles in MS SQL Server

Posted on 2002-07-04
3
Medium Priority
?
650 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 150 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

649 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