I have worked with SQL Server for many years now, back to Sybase SQL Server days and there has always been confusion over Logins and Users. Even though they typically use the same name they are separate objects. Newcomers to SQL Server always seem to confuse the two. Let's take a look....
I find it useful to imagine your SQL Server as a fortress. After all, it is protecting your precious data so the analogy is not a bad one. If your data is compromised, it may be a “resume generating event” so if nothing else, pay attention to security! To get through the front door of our fortress, everyone needs a login. The login is the object of authentication, proving who you are. SQL Server supports Windows Authentication (also known as “Trusted”) and SQL Server Authentication (“non-Trusted” – doesn’t sound too good does it? For this reason we also call this “Standard”). The nice thing about Windows authentication is that SQL Server trusts the Windows token and the password does not have to be re-entered giving an SSO (single sign on) solution. However, if you cannot guarantee Windows on the client-side, you will need to use standard logins.
Another advantage to Windows logins is the extra security support for the Kerberos authentication protocol providing mutual authentication. This means that the server is authenticated as well as the client. That protects against rogue servers in addition to rogue clients. Windows authentication also will piggy-back off the Windows password policy in effect either through Active Directory or Local security policies. This will force password changes after a pre-defined period, enforce complexity requirements and establish a maximum threshold of password attempts before a login is locked out. Now, since SQL Server 2005, standard logins will optionally follow the Windows password policy in effect on the server. Previously we had to develop our own password policy code within our applications.
When a Windows login is created, we can map it to a particular Windows User Account (not to be confused with SQL Server Database User Accounts; here’s where the confusion starts…) but usually we use Windows Groups. This means that membership of the groups has to be maintained by your Windows network administrator (hopefully that’s not you!) so whenever someone leaves or joins a department, as a SQL Server DBA you are sitting pretty. Say there are 1000 Windows users in a group called Accounting. To enable access to your SQL Server for those 1000 Windows users requires a single Windows login based on the Accounting group. However, say there is one Windows user called Eddie in the Accounting group who you don’t trust? Easy: just create a Windows login for Eddie and specify Deny access. Eddie will have Grant access from the Accounting login and Deny from his own login; since Deny always trumps a Grant he will be denied.
OK, just because you can get through the front door of our fortress does not mean you can do anything in any room once inside. The rooms of our SQL Server fortress are called databases and you will need a Database User Account per database that you need to access. The Database User Accounts are mapped to a single login and usually called the same name so can be easily confused as being the same object. Technically, a login is mapped to one or more Database User Accounts. It is the Database User Account that allows us to apply permissions to “securables” such as schemas, tables and views. We can group together multiple Database User Accounts that have similar security requirements into Database Roles. We flippantly refer to Database User Accounts as “users” but this creates great confusion since Windows User Accounts are also called users but are completely different.
We use Database roles for the same reason that we use Windows groups: to simplify security administration. It is much harder to apply permissions to each individual Database User Account than to a Database role with a meaningful name. We also have access to Server roles where we can group together logins for server-wide access.
With all this terminology confusion, wouldn’t it be nice if the SQL Server team had lunch with the Active Directory folks once in a while? I know the Microsoft campus now has more than 100 buildings but still…