Solved

My department wants to limit tables/views/stored procs based on database role.

Posted on 2007-11-20
4
163 Views
Last Modified: 2013-12-17
My colleague and I have been tasked with creating SQL Server 2005 application roles and restricting users with those roles from being able to see tables.  In my C# code, how do I know if a user belongs to that role created?  I am familiar with WindowsIdentity and WindowsPrincipal classes but those are more at the operating system (or network), not at the database.  I have not found much information on how to do this.

Thanks
0
Comment
Question by:techhound
  • 2
4 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 20324114
You can do this right in SQL server providing you have your users actually logging in as users and not as one global user .... http://www.databasejournal.com/features/mssql/article.php/1441261

Cheers,


Greg
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 20324135
If you want to "pre-check" in your code, you can use:
EXEC sp_helpuser 'username'
and/or
EXEC sp_helpuser 'rolename'


0
 

Author Comment

by:techhound
ID: 20324537
Thanks for your responses.  But I need to know if there is a way to access these roles from C#.
0
 
LVL 37

Assisted Solution

by:gregoryyoung
gregoryyoung earned 250 total points
ID: 20324542
all of what we are showing you is just stored procedures that you can call in the database so sure you can call them from C#

ex:

sp_helprolemember [[@rolename =] 'role']

you can call this sproc from C# and it returns you the role members of a given role....

or ..

sp_droprolemember [@rolename =] 'role',
    [@membername =] 'security_account'

drops a member from a role ...

etc etc

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

744 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