Solved

master db's system object counts

Posted on 2010-11-24
4
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

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 40

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

630 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