security in SQL Server 2005

LOGIN Vs USER

A LOGIN give you connection rights.

• It is stored in the MASTER database
• Applies to the instance
• Has no permissions directly per se Exception: Server Role membership
• Mapped to a user for permissions

A database USER is the permissions container

• Also the schema owner
• Permissions are granted to database users, not logins
• Specific to a single database

------------------------------------------------
Access and Authentication - Principals

Windows-level principals

• Windows Domain Login
• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User
• Database Role
• Application Role

** DENY at any level always take precedence.