concept database user in category sql server
This is an excerpt from Manning's book Learn Windows SQL Server Administration in a Month of Lunches.
Security experts often refer to the “three As” of security: authentication, or who you are; authorization, or what you can do; and accounting, or auditing, which is what you’ve done. In this and the next two chapters we’ll explore those concepts as they apply to SQL Server. We’ll begin with authentication. Are you who you say you are? SQL Server uses a two-layer system of authentication—logins and database users—with lots of variations and options. Some of these options are in the product because they’ve always been there; some of them are new concepts for SQL Server that are designed to meet changing business and operational requirements.
A login only grants you some kind of access to the entire SQL Server instance. From there, you’ll want to get into one or more databases. That happens by mapping a login to a database user inside each database that you’ll be using. Figure 6.3 shows the relationship between logins and database users.
The idea here is that one person (or group) will manage the server itself, and so they get to control who will connect to it. They do that by managing logins. But SQL Server realizes that the people who own the actual databases might be different, such as when a departmental application database needs a SQL Server to live on. The SQL Server administrator wants to control access to the server, but the department manager wants to control who gets to his or her data. So logins don’t grant any explicit access to databases. Instead, someone inside the database creates database users. Those don’t come with their own password or anything (well, usually they don’t). Instead, they reference an existing server-wide login. Permissions within the database can then be assigned to the database user, controlling what that login can do inside the database.
