Taking a poll on user authentication

[LonnieJohnson]

I have an SQL Serve 2K backend to several MS Access applications with around 300 users.

I have been setting up an individual user ID on the SQL Server and assigning them to roles according to their job.

Someone suggested that I use one ID to connect to the server and put the separate ID's in a table and check the table when they log in. However, I like the individual users for things like tracking who did what in my triggers.

Is is better to have just one SQL ID set up or individuals and why?

Thanks in advance.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[SQLBill]

Anyone who accesses my database server has a login. Whether it's Windows Authentication or SQL Server Authentication, they each have their own login. That way I can tell who is accessing my databases and what they are doing. When a user leaves and no longer is permitted access, I remove their login. With a common login, I would have to keep changing it every time someone left or database security would not exist.

-SQLBill

 

[fluteplr]

I agree with SQL Bill.

This has been one of my gripes about MS pooling connections.
Whats the point of having security in SQL server if they want to to use the same login for every one when you use conection pools?

I had about a 2 hour discussion / argument with a MS rep. one day about this.

That being said I use separate logins either SQL server or NT whenever possible.

 

[SQLBill]

There are some times that a single login is okay. Usually it's with applications not users.

I have an application that users log into and do their queries. Then the application queries the database and provides the data back to the user. In this case the application acts as a 'middle-person'. What's good about this? Well the end user isn't really seeing the database. They are only seeing what the application provides them. It's similar to creating a view and only allowing users to see the view. Nice part is the application changes the names of the columns so users don't even know the real columns of the database.

Security is still good, because the users each have a login to the application which allows them to be tracked.

-SQLBill

 

[Corran007]

Agreed. On all my web applications, i create a user that has all rights to the database. In my actual application i keep track of who has permissions to do what and could keep detailed logs if i needed to. Yes it is a bit more work, but there are some things you want to restrict a user to do that are not able to be easily defined in a database. But web applications are different due to the way a webserver works.

if i was doing client/server, i would proably go entirely user based with either each user with thier own or have one and let the application track everthing.

 

[LonnieJohnson]

Thanks guys, that shines a clear light on the situation. The person that recommened that I use a single ID builds web applications.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[timscronin]

If using SQL Authentication strongly against adding individual ID's. Create roles insided DB and then add create and NT group for each role. You can then add the NT group as a login and assign it to the role. Much cleaner and easier. DOn;t have to worry about orphaned ID's. FYI, if you use this and want to see the individual user you still get that, just go go current connections and you will see it show he user id' not the NT group id.