Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?
10
votes
3
answers
2115
views
SQL Server [System Databases](https://learn.microsoft.com/en-us/sql/relational-databases/databases/system-databases?view=sql-server-ver15) , as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate
WHERE database_id > 4
to exclude them from the actions of the script.
Also, if I run SELECT name, schema_id FROM sys.schemas;
on a new user database, I get:
name schema_id
dbo 1
guest 2
INFORMATION_SCHEMA 3
sys 4
db_owner 16384
db_accessadmin 16385
db_securityadmin 16386
db_ddladmin 16387
db_backupoperator 16389
db_datareader 16390
db_datawriter 16391
db_denydatareader 16392
db_denydatawriter 16393
I ran that query on two different instances, one a SQL Server 2016 and the other a SQL Server 2005, and both returned that same result.
Questions:
----------
1. Is there any situation (or sql server version) in which the system databases master, tempdb,
model and msdb would have a database_id other than 1,2,3,4
respectively?
2. Can I **really** trust on the fact that the schemas I listed will always have the same IDs on any instance of SQL Server so that I can write maintenance scripts based on those IDs?
Asked by Ronaldo
(6017 rep)
Feb 12, 2020, 12:25 PM
Last activity: Oct 14, 2022, 09:28 AM
Last activity: Oct 14, 2022, 09:28 AM