System databases Vs User created databases

Hi

I am using following query to get the list of db

select * from sys.databases (or select * from sysdatabases)

My question is how can we know that which db is system database and which one is user created?

Regards
Karan Gupta
KaranGuptaAsked:
Who is Participating?
 
YZlatConnect With a Mentor Commented:
One difference would be

Select *
From sys.databases
Where database_id > 5

will not return aspnetdb

and

SELECT * FROM sys.databases
WHERE owner_sid != 1

will return aspnetdb

aspnetdb is not a system database, it is the default database for using the ASP.NET Application Services
0
 
Barry CunneyCommented:
-- User Databases
Select *
From sys.databases
Where database_id > 5

--System Databases
Select *
From sys.databases
Where database_id <= 5
0
 
KaranGuptaAuthor Commented:
is 1-4 id fixed for system dbs in all versions of sql server. What is the role of sid field
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
YZlatCommented:
SID is a system-level identification number.

when an object is created it is assigned an id - SID, that number identifies the object as a securable within SQL Server and then the object is deleted, the id is recycled.
0
 
YZlatCommented:
you can also use

SELECT * FROM sys.databases
WHERE owner_sid != 1

Open in new window

0
 
KaranGuptaAuthor Commented:
so what is good practice using sid to identify db or using id what BCUNNEY has suggested
0
 
YZlatCommented:
either one will work
0
 
YZlatCommented:
I think it would be fair to split the points with BCUNNEY
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.