Solved

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

Posted on 2007-11-20
4
166 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:
Scott Pletcher 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 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