First time asking an SQL question...

Hi guys,
I've just invented a fabulous way of barring students from accessing the
Internet at the school of which I'm the network administrator.
Question is, can it be done?
Essentially, if - in a classroom environment - the teacher has decided
beforehand the students in the class do not need to access the Internet for
that class time, the teacher should be able to revoke *any student ID* from
Internet access *that logs on at any of those class room PCs*. Sounds harsh?
Having Internet access where none is needed is a GIGANTIC distraction for
kids who SHOULD be doing something else entirely. More fierce supervision -
probably, but that's not my call, and my solution is much more elegant, and
final.
The classroom PCs run Windows 98 and Windows XP, depending on the CPU
capability. Students access the Internet via ISA Server 2004 - and only if
they are members of "Internet Access".
So far, I have been able to have these PCs update which user ID is logging
in to which machine, to a central SQL database.
So thus far:-
SQL Server has a database called "WSDB"
in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and
"Room5".
In "Room1" (for example) there are 28 fields - R101, R102, R103... R128.
Meaning Room 1 PC 01, Room 1, PC 02, etc.
In the R101 field (for example), when a student logs in, his/her login ID is
stored against R101 - "ST1041", meaning the R1 table, field R101 has ST1041
user ID logging in to it.
So, if the teacher taking Class 1, in Room 1 has decided NO student ID can
access the Internet for that Class, that student ID should be removed from
the Active Directory Internet access security group. Therefore, that student
ID cannot access the Internet through our ISA Server 2004, because only
members of the Internet Access security group can get through! Hooray!
So essentially I need this:-
Teacher decides beforehand Room 1 Period 1 No Internet Access required (and
selects "No" in a database).
Student logs in on R104 Period 1.
My little program updates WSDB database/R1 table/R104 field with the student
login info.
Trigger is tripped - R104 field in table R1 in database WSDB is updated.
SQL trigger checks whether R1 Period 1 Internet access denied.
If yes (denied), remove student login ID from Internet Access security
group.
At end of Class 1, automatically (based on time-of-day) restore all student
accounts to Internet Access security group.
Can any of this stuff be done within SQL Server 2000? (As in, execute a
query/trigger/whatever?)
This is my first ever go at playing with SQL Server beyond creating a
database - muddling around I could probably get something to go...
Could the above scenario be got to work?
Mark<Comment>
This shouldnt be done in tables, you should consider having one Table for
the Rooms which is called "Rooms", there you could add for rows with a
description of the rooms..yadayada
</Comment>
In "Room1" (for example) there are 28 fields - R101, R102, R103... R128.
<Comment>
Guess you mean columns not fields, you should go for one table called
Computers referenced to the Table "Rooms". You should redesign your database
as soon as do this, this will help you in the future. believe me ;-)
</Comment>
Redesign your database, then we can look further to your requirements.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mark B" <mark@.mosaiccomputers.com.au> schrieb im Newsbeitrag
news:eZzuo0tWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I've just invented a fabulous way of barring students from accessing the
> Internet at the school of which I'm the network administrator.
> Question is, can it be done?
> Essentially, if - in a classroom environment - the teacher has decided
> beforehand the students in the class do not need to access the Internet
> for that class time, the teacher should be able to revoke *any student ID*
> from Internet access *that logs on at any of those class room PCs*. Sounds
> harsh? Having Internet access where none is needed is a GIGANTIC
> distraction for kids who SHOULD be doing something else entirely. More
> fierce supervision - probably, but that's not my call, and my solution is
> much more elegant, and final.
> The classroom PCs run Windows 98 and Windows XP, depending on the CPU
> capability. Students access the Internet via ISA Server 2004 - and only if
> they are members of "Internet Access".
> So far, I have been able to have these PCs update which user ID is logging
> in to which machine, to a central SQL database.
> So thus far:-
> SQL Server has a database called "WSDB"
> in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and
> "Room5".
> In "Room1" (for example) there are 28 fields - R101, R102, R103... R128.
> Meaning Room 1 PC 01, Room 1, PC 02, etc.
> In the R101 field (for example), when a student logs in, his/her login ID
> is stored against R101 - "ST1041", meaning the R1 table, field R101 has
> ST1041 user ID logging in to it.
> So, if the teacher taking Class 1, in Room 1 has decided NO student ID can
> access the Internet for that Class, that student ID should be removed from
> the Active Directory Internet access security group. Therefore, that
> student ID cannot access the Internet through our ISA Server 2004, because
> only members of the Internet Access security group can get through!
> Hooray!
> So essentially I need this:-
> Teacher decides beforehand Room 1 Period 1 No Internet Access required
> (and selects "No" in a database).
> Student logs in on R104 Period 1.
> My little program updates WSDB database/R1 table/R104 field with the
> student login info.
> Trigger is tripped - R104 field in table R1 in database WSDB is updated.
> SQL trigger checks whether R1 Period 1 Internet access denied.
> If yes (denied), remove student login ID from Internet Access security
> group.
> At end of Class 1, automatically (based on time-of-day) restore all
> student accounts to Internet Access security group.
> Can any of this stuff be done within SQL Server 2000? (As in, execute a
> query/trigger/whatever?)
> This is my first ever go at playing with SQL Server beyond creating a
> database - muddling around I could probably get something to go...
> Could the above scenario be got to work?
> Mark
>|||I suspect it would be possible to do such a thing... perhaps invoking
xp_cmdshell then using one of the NET commands to do the AD thing..
You might consider instead of removing the students from the Internet Access
permission group, create another group which is the DENY Internet Access
group... Always leave them in the Access group, but temporarily place them
in the deny group, and delete them from the Deny group when class is over...
That way if you ever get messed up somehow, you can simply delete everyone
from the Deny group and start again... (Leaving the Access group safe and
unchanged.)
There is also a way to setup an AD linked server (which I haven't done), or
may use something in WMI to make this easier... But I do not use either, so
perhaps someone else would have a suggestion there...
At first I was thinking why not just do the work when the teacher says Deny
for the entire class, but that would deny a student for the internet even if
he was NOT in class... So in your plan, a student would only be denied the
he attended class, but if the class was skipped (perhaps to complete some
other project) , they would not be denied... - good idea...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Mark B" <mark@.mosaiccomputers.com.au> wrote in message
news:eZzuo0tWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I've just invented a fabulous way of barring students from accessing the
> Internet at the school of which I'm the network administrator.
> Question is, can it be done?
> Essentially, if - in a classroom environment - the teacher has decided
> beforehand the students in the class do not need to access the Internet
for
> that class time, the teacher should be able to revoke *any student ID*
from
> Internet access *that logs on at any of those class room PCs*. Sounds
harsh?
> Having Internet access where none is needed is a GIGANTIC distraction for
> kids who SHOULD be doing something else entirely. More fierce
supervision -
> probably, but that's not my call, and my solution is much more elegant,
and
> final.
> The classroom PCs run Windows 98 and Windows XP, depending on the CPU
> capability. Students access the Internet via ISA Server 2004 - and only if
> they are members of "Internet Access".
> So far, I have been able to have these PCs update which user ID is logging
> in to which machine, to a central SQL database.
> So thus far:-
> SQL Server has a database called "WSDB"
> in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and
> "Room5".
> In "Room1" (for example) there are 28 fields - R101, R102, R103... R128.
> Meaning Room 1 PC 01, Room 1, PC 02, etc.
> In the R101 field (for example), when a student logs in, his/her login ID
is
> stored against R101 - "ST1041", meaning the R1 table, field R101 has
ST1041
> user ID logging in to it.
> So, if the teacher taking Class 1, in Room 1 has decided NO student ID can
> access the Internet for that Class, that student ID should be removed from
> the Active Directory Internet access security group. Therefore, that
student
> ID cannot access the Internet through our ISA Server 2004, because only
> members of the Internet Access security group can get through! Hooray!
> So essentially I need this:-
> Teacher decides beforehand Room 1 Period 1 No Internet Access required
(and
> selects "No" in a database).
> Student logs in on R104 Period 1.
> My little program updates WSDB database/R1 table/R104 field with the
student
> login info.
> Trigger is tripped - R104 field in table R1 in database WSDB is updated.
> SQL trigger checks whether R1 Period 1 Internet access denied.
> If yes (denied), remove student login ID from Internet Access security
> group.
> At end of Class 1, automatically (based on time-of-day) restore all
student
> accounts to Internet Access security group.
> Can any of this stuff be done within SQL Server 2000? (As in, execute a
> query/trigger/whatever?)
> This is my first ever go at playing with SQL Server beyond creating a
> database - muddling around I could probably get something to go...
> Could the above scenario be got to work?
> Mark
>|||> in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and

> "Room5".
> In "Room1" (for example) there are 28 fields - R101, R102, R103...
R128.
Do they teach database design at your school? Seriously, this is a very
poor desgn. In fact it would make a good textbook example of how NOT to
do it.

> Can any of this stuff be done within SQL Server 2000? (As in, execute
a
> query/trigger/whatever?)
You can't execute a trigger on login. You would need to create your own
process to do that.
David Portas
SQL Server MVP
--|||I pitty the help desk guy who is trying to figure out why some students are
sporatically having trouble accessing the internet. It would be easier for
the teacher to simply pull the network cable from the classroom's router
before class starts and then plug it back in after the class ends. The
network admin should be able to implement a convenient on / off switch for
teachers to use.
"Mark B" <mark@.mosaiccomputers.com.au> wrote in message
news:eZzuo0tWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I've just invented a fabulous way of barring students from accessing the
> Internet at the school of which I'm the network administrator.
> Question is, can it be done?
> Essentially, if - in a classroom environment - the teacher has decided
> beforehand the students in the class do not need to access the Internet
for
> that class time, the teacher should be able to revoke *any student ID*
from
> Internet access *that logs on at any of those class room PCs*. Sounds
harsh?
> Having Internet access where none is needed is a GIGANTIC distraction for
> kids who SHOULD be doing something else entirely. More fierce
supervision -
> probably, but that's not my call, and my solution is much more elegant,
and
> final.
> The classroom PCs run Windows 98 and Windows XP, depending on the CPU
> capability. Students access the Internet via ISA Server 2004 - and only if
> they are members of "Internet Access".
> So far, I have been able to have these PCs update which user ID is logging
> in to which machine, to a central SQL database.
> So thus far:-
> SQL Server has a database called "WSDB"
> in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and
> "Room5".
> In "Room1" (for example) there are 28 fields - R101, R102, R103... R128.
> Meaning Room 1 PC 01, Room 1, PC 02, etc.
> In the R101 field (for example), when a student logs in, his/her login ID
is
> stored against R101 - "ST1041", meaning the R1 table, field R101 has
ST1041
> user ID logging in to it.
> So, if the teacher taking Class 1, in Room 1 has decided NO student ID can
> access the Internet for that Class, that student ID should be removed from
> the Active Directory Internet access security group. Therefore, that
student
> ID cannot access the Internet through our ISA Server 2004, because only
> members of the Internet Access security group can get through! Hooray!
> So essentially I need this:-
> Teacher decides beforehand Room 1 Period 1 No Internet Access required
(and
> selects "No" in a database).
> Student logs in on R104 Period 1.
> My little program updates WSDB database/R1 table/R104 field with the
student
> login info.
> Trigger is tripped - R104 field in table R1 in database WSDB is updated.
> SQL trigger checks whether R1 Period 1 Internet access denied.
> If yes (denied), remove student login ID from Internet Access security
> group.
> At end of Class 1, automatically (based on time-of-day) restore all
student
> accounts to Internet Access security group.
> Can any of this stuff be done within SQL Server 2000? (As in, execute a
> query/trigger/whatever?)
> This is my first ever go at playing with SQL Server beyond creating a
> database - muddling around I could probably get something to go...
> Could the above scenario be got to work?
> Mark
>|||Hi,
Sorry guys, I got my terminologies - yes, it is one database with 5
tables, and in each table there are 28 columns. Those columns are labelled
R101, R102, R103, etc.
I'd love to have a router, but because there is already an ISA Server, a
firewall router and an upstream ISP Proxy, there's just more places a packet
could get lost. (Not to mention the cost - the public school finances can't
justify it).
That deny_internet group's a damn good idea. I might implement that in any
case.
I think my database design is OK. It's manageable, and simple.I hope the
respondent was saying it was not good because I was confusing my
terminologies.
The database is pretty simple in that it only records what student login ID
logs in on what computer (using an external program I have written).
Therefore, when the appropriate computer ID column is updated with the
student's login ID, that sets off a trigger.
I figure I could then use that trigger to check whether Internet Access
should be denied and if yes, remove that ID from the Internet Access
security group.
So.
Database = WSDB
Table = R1
Columns = R101 R102 R103 R104 R104 R106 R107 ... R128
Should a trigger be applied to the table? Or can a trigger be applied to
each column? Frogive my newbie questions. You're right - I should get a
book, but the community is much faster (and more accurate!)
Mark|||This database design is what is known as "denormalized", and it's not good.
Let's say you have up to R128 right now. What happens when they want to add
another room? Now you have to change your schema to accomodate it. Let's
say they like it so much they decide to add the whole second floor (R201 to
R228). Do you really want to keep changing your schema and re-writing the
front-end program so it can handle all the new rooms? How are you going to
get the list of rooms that are currently "Active" and those that are
"Inactive"? Normalization gives you flexibity, like this:
CREATE TABLE Rooms
(
Room_Number VARCHAR(4) PRIMARY KEY CLUSTERED,
Status CHAR(1)
)
GO
INSERT INTO Rooms (Room_Number, Status) VALUES ('R101', 'A')
INSERT INTO Rooms (Room_Number, Status) VALUES ('R102', 'A')
INSERT INTO Rooms (Room_Number, Status) VALUES ('R103', 'I')
INSERT INTO Rooms (Room_Number, Status) VALUES ('R104', 'A')
GO
In this instance, Rooms R101, R102 and R104 are set to 'A' for Active, Room
R103 is set to 'I' for Inactive. Of course you can set up your own codes,
etc.
This can be easily expanded to include separate buildings, etc., and you can
add as many rooms as you can find 4-digit identifiers for.
"Mark B" <mark@.mosaiccomputers.com.au> wrote in message
news:e69b2czWFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Sorry guys, I got my terminologies - yes, it is one database with
> 5 tables, and in each table there are 28 columns. Those columns are
> labelled R101, R102, R103, etc.
> I'd love to have a router, but because there is already an ISA Server, a
> firewall router and an upstream ISP Proxy, there's just more places a
> packet could get lost. (Not to mention the cost - the public school
> finances can't justify it).
> That deny_internet group's a damn good idea. I might implement that in any
> case.
> I think my database design is OK. It's manageable, and simple.I hope the
> respondent was saying it was not good because I was confusing my
> terminologies.
> The database is pretty simple in that it only records what student login
> ID logs in on what computer (using an external program I have written).
> Therefore, when the appropriate computer ID column is updated with the
> student's login ID, that sets off a trigger.
> I figure I could then use that trigger to check whether Internet Access
> should be denied and if yes, remove that ID from the Internet Access
> security group.
> So.
> Database = WSDB
> Table = R1
> Columns = R101 R102 R103 R104 R104 R106 R107 ... R128
> Should a trigger be applied to the table? Or can a trigger be applied to
> each column? Frogive my newbie questions. You're right - I should get a
> book, but the community is much faster (and more accurate!)
> Mark
>