Solved

master db's system object counts

Posted on 2010-11-24
4
324 Views
Last Modified: 2012-06-21
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
Comment
Question by:anushahanna
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 34209159
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34210226
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 34213323
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34249131
thanks a lot lcohan
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question