Having covered authentication yesterday, let’s move on the second A, authorization. Authentication was about proving identity. Now that we know that identity, we can determine permissions. Just because we have authentication doesn’t mean we have authorization. Let me give you an example. 

A club only permits patrons who are 21 years-old and up. A college student walks up to the entrance and hands the doorman his driver’s license. The doorman looks and realizes that the student is just two weeks shy of his 21st birthday. At this point we have identification because the ID card provides it. However, because our student isn’t 21, though he’s close, he isn’t permitted into the club. He’s authenticated, but not authorized. 

Our second A, authorization is about looking at permissions and determining what someone can do. With respect to SQL Server, it can be easy to confuse authentication and authorization for some things. 

For instance, when we add a Windows login to a SQL Server, it’s not unusual for someone to think that we are allowing SQL Server to authenticate that login. However, that’s not the case at all. What we are actually doing is granting permission to connect to SQL Server, and you can see this if you query sys.server_permissions. SQL Server will attempt to authenticate any connection. If you aren’t granted the ability to connect to SQL Server, SQL Server will still authenticate you if you are using a Windows login. It passes off the authentication to the OS. If the OS comes back and says it has authenticated the login, then SQL Server takes those credentials and checks to see if the login has any permissions to connect. If it does, it is authorized and the connection is completed. However, if the login doesn’t have the connect to SQL Server permission, SQL Server will throw an error and then it terminates the connection. The login was authenticated but had no authorization. 

The reason for the confusion is because of SQL Server-based logins. In this case, SQL Server has to perform authentication, and if that succeeds, it then has to perform authorization. It actually performs both operations. But we don’t see these two separately, hence the confusion. 

Remember, authentication is about proving identity. Authorization is about determining permissions after identity has been established.