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

x
?
Solved

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

Posted on 2007-11-20
4
Medium Priority
?
170 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
[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
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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 1000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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
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.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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