Users and Ms SQL 2000

[oyad]

plz,just new to ms sql server 2000 cos i want to work on a project for my departmental library. there will be lots of user(students) connecting to the database thru clients which on reside on a server.am thinking of not using windows authentication mechanism. users will have different levels of access to the database. where will i create information and passwords of these users. will i create and maintain a table for this Or i use the user option under the database name?
oyad

[szlamany]

Windows Authentication is the recommended method of authenticating a user.

We use it for large scale (1000+ user) enterprise systems.

The NETWORK people prefer it because they can use WINDOWS GROUPS to further ease the burder of adminstrating the access.

We further protect our DATABASE and TABLES by only granting access via STORED PROCEDURES. No user will ever be able to get into ACCESS or EXCEL and attach a TABLE a look at data.

Within our application, after we make a successful WINDOWS AUTHENTICATION connection to the database, we look at a USERNAME/USERROLE set of tables to determine what further "application levels of access" the user should be given. These tables tell us that a user might not be able to see a particular set of reports, or menu options for various maintenance screens.

[oyad]

Windows Authentication is the recommended method of authenticating a user.

We use it for large scale (1000+ user) enterprise systems.

The NETWORK people prefer it because they can use WINDOWS GROUPS to further ease the burder of adminstrating the access.

We further protect our DATABASE and TABLES by only granting access via STORED PROCEDURES. No user will ever be able to get into ACCESS or EXCEL and attach a TABLE a look at data.

Within our application, after we make a successful WINDOWS AUTHENTICATION connection to the database, we look at a USERNAME/USERROLE set of tables to determine what further "application levels of access" the user should be given. These tables tell us that a user might not be able to see a particular set of reports, or menu options for various maintenance screens.

I don't too much understand this authentication stuff, i guess we need to talk in this post, any chance?

[szlamany]

I don't too much understand this authentication stuff, i guess we need to talk in this post, any chance?

I'm heading out the door - back in about 45 minutes - feel free to ask any questions you would like!

[vincentg]

plz,just new to ms sql server 2000 cos i want to work on a project for my departmental library. there will be lots of user(students) connecting to the database thru clients which on reside on a server.am thinking of not using windows authentication mechanism. users will have different levels of access to the database. where will i create information and passwords of these users. will i create and maintain a table for this Or i use the user option under the database name?
oyad

In that case, my suggestion is create a table which list all the student's info (ID, Last Name, First Name) and a corresponding password for each. You can use only one SQL Server Username (dont use the sa account) which enables your application to access the SQL Server.

[brucevde]

SQL Server can be placed into one of two Authentication modes - Windows Authentication Mode or Mixed Mode.

Mixed mode allows you to use Windows Authentication and SQL Server Logins.

SQL Server stores user logins/passwords internally. You don't need to keep track of anything yourself. You grant permissions to these logins or place the logins into Roles and grant permissions to the Roles.

If you are using Enterprise Manager to create logins
Open the Server
Open the Security Folder
Right Click Logins
Click the New Login... menu item.
Fill in the blanks.

Once the logins are created give it the required permissions.

There are also system stored procedures to create logins, grant permissions etc. etc.

[szlamany]

Ok - I'm back...

First question that needs to be answered is "who" will be logged onto the PC itself.

Will each student log in with a WINDOWS USERNAME and PASSWORD to the PC?

[oyad]

Ok - I'm back...

First question that needs to be answered is "who" will be logged onto the PC itself.

Will each student log in with a WINDOWS USERNAME and PASSWORD to the PC?

'cos the systems belongs to all students, evert body is free to use any system. no student has a specific windows account or group. when the system is switched on in da morning, those in charge can log in with administrator or any account but not guest nor limited account. so any student can do anything on the system
now comes in the program, its the program that is trying to structure the students into group accounts, the executives of the association will have a differnt account type, so including others like final students, members of programmers club etc(about seven differnet group accounts)

[szlamany]

Then, as you indicated, WINDOWS AUTHENTICATION won't help you at all...

With that said, then I would follow what BRUCEVDE has said in his post...

Store the USERNAME/PW in SQL SERVER, under SECURITY/LOGINS. There is no reason for you to re-invent the wheel and create you own username/password set of tables.

You should be able to use the ROLES available in SQL to assign different levels of access to these SQL USERS, and as mentioned, there are stored procedures to find out what ROLES a user has, so in VB you can find out what level person is connected.

[oyad]

Then, as you indicated, WINDOWS AUTHENTICATION won't help you at all...

With that said, then I would follow what BRUCEVDE has said in his post...

Store the USERNAME/PW in SQL SERVER, under SECURITY/LOGINS. There is no reason for you to re-invent the wheel and create you own username/password set of tables.

You should be able to use the ROLES available in SQL to assign different levels of access to these SQL USERS, and as mentioned, there are stored procedures to find out what ROLES a user has, so in VB you can find out what level person is connected.

Thanx man, am feeling u for ur assistance, am still new to this Ms SQL Server stuff(formerly on access), i can't figure out what u're trying to say. have been to the roles and users stuff under the database, but can't just work anything out there?!?1

[szlamany]

Thanx man, am feeling u for ur assistance, am still new to this Ms SQL Server stuff(formerly on access), i can't figure out what u're trying to say. have been to the roles and users stuff under the database, but can't just work anything out there?!?1

Do you have books online (MSDN BOL)??

If so then look up the topic "APPLICATION ROLES".

Otherwise go to www.msdn.com and look around there - topics are harder to find, but the information is extensive...

I've never used APPLICATION ROLES - but they do seem to fit your requirements from what I know of them.