May include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles
Let’s start with the boring definitions: authentication is the act of identifying yourself, while authorization is that one when you gain access to resources, based on your identity.
SQL Server allows two types of authentication methods: Windows authentication allows you to connect SQL Server with an existing Windows account , while SQL Server authentication allows connections from anywhere – as long as you set it up this way (which is a bad idea). You should use Windows Authentication, because this way you make your life easier (don’t have to store passwords in config files to connect), but if it isn’t possible, SQL Server authentication is the way to go.
Now how to build up your authentication model – there’s an easy way to go – connect with a fixed application credential. This way you can control what the app can do in the database server. However, sometimes you need more granularity – let’s say you are interested in who did what. If you connect with a single application credential, you’ll won’t get user-detailed information. Then you should use built-in user accounts.