I have a DB which have three types of users: students, admins, and volunteers. I am trying to use SQL Server Enterprise Manager 8.0 to set up those roles such that students can (where appropriate) select, insert, update, or delete from given tables/rows; similarly for admins and for volunteers. (The database was set up with no roles other than the standard ones and everyone was called a db_owner, I am assuming with wide-open permissions.)
The ultimate goal is to have a database that various users can sign into through a web-page interface and have them be only able to play in their appropriate sandbox given the role with which they are set up. Person A will be able to register as a student or a volunteer and from thence do only studently or volunteerish stuff, and only another admin can create another admin (that is, you can't go to the register me page and say 'I want to register as an admin' and have that work without human intervention--but students and volunteers should be able to freely).
I have a couple of questions. First, when I get to the Permissions tab of the Database Role Properties screen, I'm not sure what DRI means, and I'm not sure what the difference is between clicking on a box so that it's got a green check, clicking on it so that it's got a red x, and clicking on it so that it's once again blank.
Second, after I've made my changes (and maybe I'm not doing them correctly) it seems to have no effect on my ability to pull from any table I want from a user in the student role (for example). I suppose it requres that I restart the service, but I'm not sure how to do that other than rebooting the system. Is there an easier softer way? Also, is there a screen from within Enterprise manager from which I can do sample queries? I'd have more confidence in doing that than I do in the current state of the Perl scripts I have been using for proof-of-concept checking.
The mechanics:
SQL Enterprise Manager 8.0
Internet Information Services 5.1
Windows XP Professional, Version 2002, SP2 installed
Dell Pentium 4
Sorry for the long post. Thanks for any insight anyone can offer.