When SQL Server Management Studio (SSMS) talks to the database engine it uses the same system objects, tables and views that are available for any other database client to use. (The same can’t be said for system stored procedures which use crazy system-only functions. I mean check out the definitions of any system view or procedure with sp_helptext)
Eavesdropping On SSMS
So we can actually watch SSMS talking to the database using Profiler. We can take a look “behind the curtains” so to speak.
Here are the profiler settings that I always use:
- Default trace template
- Application Name LIKE ‘Microsoft SQL Server Management Studio%’
- Hostname = <my computer’s name>
This lets us see the queries that SSMS is sending to SQL Server. It’s a trick I use often to give me an idea of what system views and objects might be important when managing SQL Server. (And managing SQL Server includes automating management tasks). So now I want to show you some examples.
SSMS Things I Wonder About
Here’s a few things that I wonder about. They’re not necessarily important things on their own, but they’ll help me show how to use profiler to look inside SSMS.
Thing 1: What’s that red down-arrow over a database user. It reminds me of a database that’s been brought “offline”, but I’ve never heard of an “offline” user.
Thing 2: When scripting views, how does SSMS retrieve definitions?
Thing 3: I have a database that is restored, but without recovery. SSMS shows it with a green up-arrow and with appended text (Restoring …). How does it know which databases are in that state?
No Longer Wondering
So here’s what I found.



This Goes For Any Application
To satisfy curiosity, I’ve profiled lots of other database applications on my development machine and you can too.
If you’re a real keener, and you want a self-guided deep dive into SQL database internals. Try profiling Danny Gould’s Internals Viewer. It’s an eye opener.
Cool stuff, as usual!
Now I’m probably going to spend all day “looking behind the curtain”.
No wait… scratch that… Instead I’m going to spend all day looking at the XKCD color pallete… I love some of the color’s names… “snot green” (which is subtly different from “booger green” and just plain “snot” and just plain “booger”), “ugly brown”, “vomit”, “sh*t”, “purpley pink”, “macaroni and cheese”, etc, etc.
Oh well, no work getting done today… Thanks a bunch. 8^)
–Brad
Comment by Brad Schulz — June 29, 2011 @ 1:56 pm
@Brad, I know right?!
For the confused, there’s hidden “hover text” over the image that says “The colour I used here is ’emerald’. Click for a really cool palette”. When you click the illustration, you are brought to http://xkcd.com/color/rgb/, which are nearly 1000 colours, as defined by participants in the xkcd color name survey.
Comment by Michael J. Swart — June 29, 2011 @ 2:16 pm
[…] Poking Around Inside Management Studio – Satisfying his curiosity this week Michael J. Swart (Blog|Twitter) goes poking around. […]
Pingback by Something for the Weekend – SQL Server Links 01/07/11 — July 1, 2011 @ 7:47 am
[…] Try using profiler to see what’s going on (what queries are used), as suggested at the end of this blog post: https://michaeljswart.com/2011/06/looking-inside-management-studio/ […]
Pingback by SQl Server Internals Viewer » SQL Server and Beyond — July 14, 2011 @ 3:12 am
Just for #1; it may not be that the user doesn’t explicitly have connect permission but that someone has REVOKED the default connect permission from the public database role.
Comment by Cody — February 5, 2016 @ 3:06 am