What’s in a name? When it comes to different Relational Database Management systems (RDMS) it can be confusing. The same words have different meaning depending on the software. SQL Server Database Engine has Instances, Logins, Databases, Schema, and User. When a DBA is used to a different database platform the terms can cause some confusion. Below are the terms as they apply to SQL Server. I have also included a link to a blog posted by XTIVIA’s Oracle experts on the meaning of these terms for Oracle.
Instance:
The instance of SQL Server is single installed components of SQL Server. This includes binaries, memory allocations, collation settings, and system databases (Master, MSDB, TempDB, and Model). It is also the collection of user databases associated with the instance. You can install up to 50 different instances of SQL Server on a single server. One default instance and the rest would be named instances.
Logins:
Login is at the instance level for SQL Server. There are 2 basic authentication types of logins for SQL Server: Windows Authentication or SQL Server Authentication. For more information about the Authentication go here.
Logins are used to set Instance level roles and permissions. This is also referred to as the Server principal.
Database (Also called User Database)
A database is made up of the physical files that contain the data and objects. It includes Filegroups, Data Files, Log files. You can have up to 32,767 databases on a single instance of SQL Server.
Schema:
A schema is a collection (or container) of objects in a Database. These objects include Tables, Indexes, views, stored procedures, functions. For SQL server, there is a separation of User and Schema making it easier to remove a user from a database without affecting the objects created by the user.
User:
A user is an account\group in a given database. It is also called Database principal. Security is tied to a user for permissions of what activities can be completed. Data Manipulation Language (DML) or Data Definition Language (DDL) commands are granted to the user either explicitly or via database roles.
Understanding the general database terminology and how it is implemented for the given RDBMS software you are using. When talking to other database professionals who support different database systems, it is important to understand the differences in the command words.