Professional Microsoft® SQL Server® 2008 Administration

9.5. Identity and Access Control

Now that you understand the operating system configuration, how SQL Server uses the operating system security, the surface area configuration of SQL Server, and how to communicate with SQL Server via endpoints, it's time to talk about authenticating against SQL Server itself.

Quite simply, logins, as they are traditionally called, permit one to connect to SQL Server. Starting with SQL Server 2005, Microsoft began using the term server principal because of some confusion over the word user. In SQL Server nomenclature, user means a security principal within a database. In other words, for a particular database, if a login has access, it is mapped to a user. However, we tend to use the word user to refer to a person (such as an end-user) or a Windows user account. In an attempt to clear things up, Microsoft began using the term "server principals" where we would traditionally use the word "logins," and "database principals" where we would use the word "users."

You'll still see the older terms used. In fact, the T-SQL language uses LOGIN and USER to manage server and database principals, respectively.

If you're still struggling over the difference between a login (server principal) and a user (database principal), imagine SQL Server as a building, perhaps a shopping mall, that requires a key for entrance. Logins are the equivalent of that key. Inside the mall, each individual store is locked and secured by its owner. If you have a key to open a particular ...