Michael J. Swart

June 29, 2011

Poking Around Inside Management Studio

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , , , — Michael J. Swart @ 12:00 pm

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.

Question
What’s that red down-arrow over a database user?
Profiler Info
When I refreshed the Users node in Object Explorer, I saw this in profiler.
What this means
Unsurprisingly, the red arrow means user has no db access and it doesn’t mean disabled user. In other terms, SSMS is looking for a list of users and whether or not they have db access. DB Access here is determined by whether users have been granted (database_permissions.state is ‘G’ or ‘W’) permission to connect (database_pemissions.type=’CO’).
Books Online
I couldn't find links for the Users node in Object Explorer, but here’s the documentation for sys.database_permissions.

Question
When scripting views, how does SSMS retrieve definitions?
Profiler Info
When I scripted a view to clipboard, this is what I saw in profiler.
What this means
So in this case, SSMS is looking to sys.sql_modules for the definition of the stored procedure. That’s good and it’s what I expected. I was a little afraid that it would use sys.syscomments (which was so 2000). But I was surprised that it also uses a view called sys.system_sql_modules in case the procedure was a system one. I was unaware of that view.
Books Online
Point for Microsoft, they document how to script objects well.

Question
How does SSMS know which databases are in a Restoring state?
Profiler Info
When I refreshed the Databases node in Object Explorer, I saw this in profiler.
What this means
If you squint at the SQL – a highly effective method for understanding SQL – you'll see that we can determine the restore status of a database by looking at the state column of the table master.sys.databases. In this case, when state=1 the database is in the Restoring state. There was actually nothing too surprising here. In this case, SSMS's queries match my expectations.
Books Online
Kudos to Microsoft again. Here is their docs for sys.databases (with more information on that state table).

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.

5 Comments »

  1. 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

  2. @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

  3. […] 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

  4. […] Try using profiler to see what’s going on (what queries are used), as suggested at the end of this blog post: http://michaeljswart.com/2011/06/looking-inside-management-studio/ […]

    Pingback by SQl Server Internals Viewer » SQL Server and Beyond — July 14, 2011 @ 3:12 am

  5. 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

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress