SQL Server provides several default system databases, each serving a specific purpose. Here are the main ones:
master: This database stores all the system-level information for an instance of SQL Server. It includes server configuration settings, logon accounts, linked servers information, startup stored procedures, and file locations of user databases.
msdb: This database is used by SQL Server Agent. It supports jobs & alerts, Database Mail Service, Service Broker, and stores the backup & restore history for the databases.
model: This database serves as a template for creating other databases. When you create a new database, SQL Server copies the contents of the model database to the new database.
tempdb: This database stores temporary user objects that you explicitly create like temporary tables and table variables. Also, the tempdb stores the internal objects that the database engine creates.
resource: This is a read-only database that keeps system objects that are visible in every database on the server in the sys schema.
In addition to these, SQL Server also provides several predefined database-level roles. These roles are security principals that group other principals, similar to groups in the Microsoft Windows operating system. There are two types of database-level roles:
- Fixed-database roles: These are predefined in the database and exist in each database. For example, members of the
db_ownerdatabase role can manage fixed-database role membership. - User-defined database roles: These are roles that you can create.