Michael J. Swart

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.