This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
asked
Difference between [sys]-schema views in master and msdb?
*Information: This question has its reference from [one of the questions in the forum][1] wherein I had some discussions with @Fatherjack, however an answer couldn't be gathered hence re-posting..* Wanted to know as to just how different is the sys.server_principals in msdb database and master database? Why was there the need to place the view (and other such similar [sys]-schema views) in two different system databases? As per initial discussions with @Fatherjack (seen in the comments of [that post][2]) , even the query plans are identical. So, is there any reason why the views were duplicated in master as well as msdb? [1]:
http://ask.sqlservercentral.com/questions/106920/how-to-list-login-the-database-it-can-use-and-fixe.html [2]:
http://ask.sqlservercentral.com/questions/106920/how-to-list-login-the-database-it-can-use-and-fixe.html
Are you sure that what you're seeing is literal duplication of these systems views? If you check all your databases you'll see sys.server_principals. That's because it's a system-wide view showing you the same data from any database that you query it from. I just looked at AdventureWorks2012 and you can see it there too.
Air seems a slightly odd comparison, but sure. No differences, especially server level objects. There are also database level system objects and they will show different data depending on the database. Nope. Not master. There are in the Resource database:
http://msdn.microsoft.com/en-us/library/ms190940(v=sql.110).aspx
Hello Grant, I did not mean a literal duplication, what I was trying to understand was if; as you say; that its purpose is to show you the SAME data from any database that you query it from, then why to distribute it across all databases and not keep it at one place? I think I am missing something crucial in the jigsaw, hence this query. So please do help me understand.
It's not distributed all over the place. It's visible all over the place.
Okay. So do you say that they are the same thing but present everywhere-like air!? And if you were to re-direct to the post where it all started, via the link given in the question, do we infer that there is no difference between msdb.sys.server_principals and master.sys.server_principals-as both will give the same answer to the question at hand? If answers to the above are Yes, an additional clarification: Where do the [sys]-schema views originate from? My answer is master database - Is it correct?
Ahh..that finally gives me the crucial missing piece of the jigsaw puzzle. Thanks a lot Grant! PS: Forgive the air-analogy, missed to hit the right note, but hey! am glad the point was conveyed through to you. :)
15 People are following this question.
