When I discuss SQL Server security, one of the basic concepts I concentrate on is the difference between logins and users with respect to SQL Server. As of SQL Server 2005, Microsoft introduced new terminology, that of server and database principals. So the mappings look like:

• login = server principal
• user = database principal

However, when you look at the T-SQL given to create these objects within SQL Server, and the T-SQL was also introduced in SQL Server 2005, you find that they are CREATE LOGIN and CREATE USER respectively. So logins and users will remain a part of the nomenclature for the foreseeable future. Because of this, and because we are often very ambiguous when we use the term USER, I looked for a way to better communicate what SQL Server means when it uses LOGIN and when it uses USER. I finally came up with the self-storage facility model, which seems to work well. For instance:

In order to get into most self-storage facilities, you usually have to enter a code. For instance, there's a keypad at the gate of one such self-storage facility.

You can see the individual self-storage units inside. If the facility itself is considered the SQL Server and the individual self-storage units are the databases, then the first thing you must do is enter the facility. You do so by typing in the passcode. This is the equivalent to having a login. A login allows you to enter the SQL Server. However, that doesn't mean you have any access to any of the individual self-storage units, aka databases. That's because when we do a close-up on the storage units, we see:

Along the right side there's the place for a lock (or multiple locks) to go. We'll assume just one. In order to get into the unit, you have to be able to open the lock. In the real world that means either having the key or having the combination. Once you open the lock, you can enter the facility. This corresponds to having a user account in the database. For each database where you have the "keys" (you are a user), you can enter.

So what about master, msdb, and tempdb? Well, in those cases everyone comes in as guest (with the exception of members of the sysadmin role or anyone who has been granted CONTROL SERVER rights, in which case you come in as dbo). That's easy to fit into this model, too. Those are storage units where there is no lock. Everyone who can enter the server can get into them. And that's by design.

Now once you're in the database, there are permissions at the schema level, on the individual objects, etc. Once you get into a storage unit, there's nothing stopping someone from putting a safe, a locked box, or some other secured storage container in place. If you think of tables, stored procedures, schemas, and the like as those individual components that can fit inside the storage unit, that completes the model.