Database Role (predefined set of permissions; role is assigned to user so he automatically gets these permissions)

Application Role (allows application to run with its own, user-like permissions, “access to specific data to only those users who connect through a particular application”, “Unlike database roles, application roles contain no members and are inactive by default, are enabled by using sp_setapprole, which requires a password.”

Schema

Named collection of DB objects which form a namespace (create objects and schemas, associate objects with schemas)
User vs. Schema: objects in 2005 have the following fully qualified naming convention: server.database.schema.object. Advantages of using schema:

Previously user was in the place of the schema, which either created problem since different users own different parts of schema and the change is not easy, or restricted the user to always be dbo.

Ability to group related objects. Previously objects had to be prefixed to identify different components of the more complex application. With schema objects in the same database can belong to different schemas.

Simplified permissions management: can give permissions on Schematic level, rather than on object level.

Fully qualified naming conventions are only used in certain cases (e.g. for linked servers). Server name can be omitted if connected to one server; db name can be omitted, if specified default db, and schema name can be omitted if specified default schema.

Creating a login

Right lick on Logins, choose New: create and manage Windows and SQL Server principals. Has the following tabs:

General Select user type and specify user/group name. Certain options (e.g. Enforce password policy/expiration, user must change password on next logon) are only available for SQL Server authentication. Also allows to specify default database. Enforce password policy (uses local policy of the machine on which it’s installed) is only available if installed on Windows 2003 or above. Enforce password expiration is only enabled if Enforce password policy is set. Also has advanced features of SQL Server authentication: Mapped to certificates/asymetric key.

Assigning database roles and permissions

Schemas allow creating schemas and configuring permissions per schema. Each schema has ONE owner (e.g. dbo). Tables are created and associated with schemas (in table properties). Database users are created and mapped with SQL Server logins (database user might have the same or different name than server user). SQL Database user can have default schema (similar to how server user can have default database), but Windows users cannot have default schema.

When creating a database user:

General tab: specify user name, choose existing login name to associate with, choose default schema. Also allows to set which schemas user owns (but each schema has only one owner, so it replaces the owner). And allows to choose database roles.