A SQL Server instance is the engine. A database is one set of data that the engine looks after. People use the two words as if they mean the same thing, and then cannot work out why a login works everywhere except the one place it is needed.
Three Levels, Not Two
Start with the building. The machine is the building. An instance is a flat inside it, with its own front door, its own meter and its own locks. A database is a room in that flat.
One machine can hold several instances. Each instance has its own version, its own patch level, its own memory setting and its own service that starts and stops on its own. Each instance then holds many databases, and those databases share the instance’s memory and its CPU.
This query tells you where you are standing:
SELECT @@SERVERNAME AS server_name,
SERVERPROPERTY('InstanceName') AS instance,
DB_NAME() AS current_database;server_name instance current_database
focus\SQLDEV SQLDEV BasicsLabThe machine is focus. The instance is SQLDEV. The database I happened to be in is BasicsLab. A default instance returns NULL for the instance name and is addressed by the machine name alone, which is why the backslash appears on some connection strings and not others.
What Each Level Owns
This is the part worth memorising, because almost every confusion comes from putting something at the wrong level.
The instance owns logins, server roles, linked servers, SQL Server Agent and its jobs, the memory and CPU settings, and the system databases master, model, msdb and tempdb. Every database on the instance shares that tempdb, which is why one badly behaved query can slow down a database it has nothing to do with.
The database owns tables, views, procedures, users, schemas and its own files on disk. A database is also the unit you back up and restore. You do not back up an instance.
Logins and Users Are Not the Same Thing
Here is the practical result of all that, and the reason people get stuck.
A login lets you in the front door of the instance. A user lets you into one room. They are separate objects at separate levels, and creating one does not create the other.
SELECT COUNT(*) AS server_logins
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') AND name NOT LIKE '##%';
SELECT COUNT(*) AS users_in_this_database
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G');server_logins 9
users_in_this_database 4Nine people can get through the front door. Four of them have a key to this particular room. Somebody who can connect to the server and then gets told the database is inaccessible has a login and no user.
There is a classic version of this. You restore a database from another server, and a login that worked there fails here. The user came across inside the backup, the login did not, and the internal identifier linking them no longer matches. That is the orphaned user problem, and it exists entirely because these are two levels.
How Many Databases Is Normal
More than people expect. My laptop instance is carrying seventeen, and that is a development machine with sample databases on it.
SELECT COUNT(*) AS databases_on_this_instance FROM sys.databases;Four of those are always the system databases. Everything else is yours.
Which Line Do You Draw
The question comes up whenever you host several customers, teams or applications. There are three places to separate them, and they cost different amounts.
Separate schemas inside one database is the cheapest. One backup, one set of maintenance, and you can query across them easily. The weakness is that a single restore brings everybody back, so you cannot recover one customer alone.
Separate databases on one instance is the usual middle ground. Each one backs up and restores by itself, and permissions are clean. They still share memory, CPU and tempdb, so a heavy neighbour is felt by everyone.
Separate instances is real isolation, including separate memory limits and separate versions. It also means separate patching, separate monitoring and more licensing, so it is the answer when the isolation is genuinely required and not before.
Most of the time the middle option is right. The mistake I see is choosing schemas for the cheapness, then being asked two years later to restore one customer to yesterday afternoon.
An instance is not a bigger database, it is the engine that several databases have to share.
This post was rewritten from scratch in September 2026. The original, published on 2013-01-27, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
