MSSQL with one or more users

[filmar]

People, can u in few words explain to me which method is better: to use MSSQL own auth system - logins or to use one login for connection and make own auth subsystem?
Of course both methods works but i wanna know pluses and minuses of both methods.


Regards

[iburyak]

It is not a right question to ask it depends on level of security you want to achieve.

In case of a windows authentication:
If I, for example, logged in to my computer and left the room, anyone can stop by and get into the database. But on the other hand you don’t have to do anything to get to the database. Users who got rights because they need to use some applications that connect to the database can easily get to the server itself and change any data they want on the back end. Some of them really think that they are very smart and know what they do. Especially when they try to update\delete with no WHERE condition or just do counts on millions of records (this can use up all server resources and stop the server from responding).

In case of a password protection:
If you have password protected login it is harder to get to the server you need to know a password. Also in very secure and protected environments (I work in one) password can be provided through a front end applications where they hashed before actually logging in to SQL Server. So in this case regular user doesn’t even know his actual password to get to the server directly and each action of such user can be monitored and recorded.

So in first case it is less secure but much easier.
In second case it is opposite more secure but harder to get to the server.

It is the same as your mail box. Do you want your friends or relatives being able to get to your mail or you would put a password and will protect it from nosy readers.

Hope I answered your question.