Solved

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

Posted on 2007-11-20
4
164 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 to return specific rows and columns, with various degrees of sorting and limits in place.

867 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

12 Experts available now in Live!

Get 1:1 Help Now