Understanding sys user / schema

Question

Understanding sys user / schema

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
March 17, 2016 at 8:30 am

#399877

Hi all,
I am puzzled by "sys".
-- use testdb;
In a testdb database -> security -> users -> i saw sys.
In the same user database -> security -> schemas -> i saw sys and its owner = sys.
So sys schema owner is sys, but double click on user "sys" under "Owned schema"
1) I do not see sys owning the sys schema.
2) 1) I do not see the sys schema
q1) why ?
===
As i am now inside testdb database. I would believe select from sys will reflect data from the sys schema INSIDE testdb.
select db_name(), * from sys.databases;
I saw both user databases and system database;
q2) I thought the master database would store databases information,
so querying sys.databases in master db would allow me to see all the other databases.
Why querying sys inside user database allow me to do the same ?
is sys schema inside userdb = sys schema in masterdb ?
Regards,
Noob

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 1

Hi,

The answer to the second question is because the sys.databases is marked as a system object meaning it can be called from any database, so no matter which database you are calling sys.databases from you will always be running master.sys.databases.

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 2

SQLSlammer (3/17/2016)
Hi,
The answer to the second question is because the sys.databases is marked as a system object meaning it can be called from any database, so no matter which database you are calling sys.databases from you will always be running master.sys.databases.

Hi SQLSlammer,

Thanks and sorry for the late reply.

How do we verify that the sys.databases we called from any user database is actually calling from master.sys.databases ?

Regards,

Noob

Lowell SSC Guru Points: 323518 More actions · Answer 3

szejiekoh (3/20/2016)
SQLSlammer (3/17/2016)
Hi,
The answer to the second question is because the sys.databases is marked as a system object meaning it can be called from any database, so no matter which database you are calling sys.databases from you will always be running master.sys.databases.
Hi SQLSlammer,
Thanks and sorry for the late reply.
How do we verify that the sys.databases we called from any user database is actually calling from master.sys.databases ?
Regards,
Noob

the sys schema is reserved, meaning you cannot create any objects in that schema.

because of that, there can only be one sys.databases, since you cannot create a per-db duplicate.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 4

Lowell (3/20/2016)
szejiekoh (3/20/2016)
SQLSlammer (3/17/2016)
Hi,
The answer to the second question is because the sys.databases is marked as a system object meaning it can be called from any database, so no matter which database you are calling sys.databases from you will always be running master.sys.databases.
Hi SQLSlammer,
Thanks and sorry for the late reply.
How do we verify that the sys.databases we called from any user database is actually calling from master.sys.databases ?
Regards,
Noob
the sys schema is reserved, meaning you cannot create any objects in that schema.
because of that, there can only be one sys.databases, since you cannot create a per-db duplicate.

Hi Lowell,

Do you mean the sys schema is reserved across databases ?

But I am seeing the sys schema in master db, as well as the user db.

(is everything in userdb 's sys refering to the masterdb sys schema) ?

But viewing sys.databases on any databases yield the same result though.

Or is it that there is only 1 sys.databases across all databases ?

In that case, how does the other userdb reference the sys.databases in masterdb (through a view ?)

Regards,

Noob

Lowell SSC Guru Points: 323518 More actions · Answer 5

All sys objects are either views, or procs or functions. The views are based on the uneditable resource database.

It is not possible to create your own obects in the sys schema, so you cannot create sys.myspecialtable or sys.myspecialproc for example. Only microsoft can do that.

You can create new objects, but they appear as new rows of data in sys.objects and sys.columns for example.

So my point being sys.databases is a special view that lists all the databases on the server. Microsoft put it in the master database. So there is only one of them. Sys.server_principals is another example. Its all the logons for the server. Theres only one view on the entire server with that name.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 6

thank you Lowell, guess I just got to play around with it more.

it seems like some sys.objects are database dependant,

select * from sys.sysusers; -- show only those users in that particular database

select * from sys.databases; -- show every database

Regards,

Noob