master db's system object counts

use master
select name from master.sys.all_objects
use tracker --user db
select name from master.sys.all_objects
both bring the same number of records --1839

select object_name(major_id) from master.sys.database_permissions dp
join master.sys.database_principals p on dp.grantee_principal_id = p.principal_id where object_name(major_id) is not null
brings the same number of records in both master and the user db.--1670

but
combining them,
select name from master.sys.all_objects where name not in
(select object_name(major_id) from master.sys.database_permissions dp
join master.sys.database_principals p on dp.grantee_principal_id = p.principal_id where object_name(major_id) is not null)

brings 174 in user db, and 169 in master db.. what may be the reason for the difference?
LVL 6
anushahannaAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
you should be able to easyly find the difference between each user DB and master system objects by running the query below - not all sysobjects are identical in all databases including system dbs:

create table ##temp1 (name sysname)
create table ##temp2 (name sysname)

use master
insert into ##temp1
select name from master.sys.all_objects where name not in
(select object_name(major_id) from master.sys.database_permissions dp
join master.sys.database_principals p on dp.grantee_principal_id = p.principal_id where object_name(major_id) is not null)

use ReportServer
insert into ##temp2
select name from master.sys.all_objects where name not in
(select object_name(major_id) from master.sys.database_permissions dp
join master.sys.database_principals p on dp.grantee_principal_id = p.principal_id where object_name(major_id) is not null)

select name from ##temp2 except select name from ##temp1;

drop table ##temp1;
drop table ##temp2;
0
 
anushahannaAuthor Commented:
excellent...

i got these 5 back..


spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values

apparently, these only belong to user DBs and not master?
0
 
lcohanConnect With a Mentor Database AnalystCommented:
thats how it looks like...if you google the name of one of them you will find something like an old bug related to some SQL 6.5! so I bet they are some cluster leftovers legacy code coming from SQL model database and dated old days when sql engine was almost as good as sybase....

http://support.microsoft.com/kb/176835

I won't worry about them but also do not drop them as Microsoft may not have removed them for some unknown to us reason.

0
 
anushahannaAuthor Commented:
thanks a lot lcohan
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.