Solved

Roles in MS SQL Server

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server computed columns 11 31
TSQL - How to declare table name 26 31
SQL SELECT query help 7 41
sql server query 6 9
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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