[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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?
0
anushahanna
Asked:
anushahanna
  • 2
  • 2
2 Solutions
 
lcohanDatabase 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
 
lcohanDatabase 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

Featured Post

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now