An Overview of SQL Server Security

SQL Server security is built on a two-tier model. The first tier is access to SQL Server, which involves the person attempting to connect being authenticated as a valid SQL Server account, or login as it is known. Think of a login as being similar to entering an office tower and signing in with the security guard. The guard verifies that you have business in the building, and you head for the elevators. The second tier involves access to the databases. As SQL Server supports multiple databases, each database has its own security layer that provides access to that database through accounts known as users. These users are then mapped to the server logins to provide access. As users are created on a database-by-database basis, access can be restricted to one or many databases as needed. If you go back to the office building example, this would be like having an access card for the elevator that only allows you to get off at certain floors.

The key points to remember in SQL Server security are that logins are server-wide and give access to SQL Server, while users are database-specific and give access only to the database in which they are created. By mapping logins to users, a connection is made to SQL Server, and access is allowed to the database.