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

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
LVL 1
James CochraneB2B FINTECH WRITER/Technology WriterAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
If you want to "pre-check" in your code, you can use:
EXEC sp_helpuser 'username'
and/or
EXEC sp_helpuser 'rolename'


0
 
gregoryyoungCommented:
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
 
James CochraneB2B FINTECH WRITER/Technology WriterAuthor Commented:
Thanks for your responses.  But I need to know if there is a way to access these roles from C#.
0
 
gregoryyoungConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.