Login form

[martin.s.ransome]

I need some advice please. Is it best to use a SQL Server Login, user or a table that stores a user name and password and why?

 

[jmcilhinney]

The SQL Server login and user go hand in hand. If you use one then you use the other as well. The login is an object at the server/instance level and it contains the credentials, i.e. the user name and the password. The user is an object at the database level and it contains the database permissions. That way, the same login can have different privileges, and maybe none at all, for each database.

It really depends on whether you want to uniquely identify each user at the database level. If it's important for the database to be able to differentiate the users then you'd have to use separate logins/users in the database. If it's OK for the database to simply know that it's that application connecting and not which user is running the app then a user table would be easier.

Note that creating logins/users in the database is not a trivial operation. If you want maintenance of users to be something that can be handled simply at the application level then a user table would be the way to go.

In an intranet situation, there's also the option of using Windows authentication. This allows you to uniquely identify each user at the database level without having to create logins/users manually. All the user maintenance would be done in Active Directory.