Solved

master db's system object counts

Posted on 2010-11-24
4
331 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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