SQL: Been told you can't access master database system views in Azure SQL Database? Not true!

When you work with Azure SQL Database, you’ll quickly learn that you send queries to a single database, and you can’t execute USE statements to change to another database. Importantly, you also can’t use four-part names to access objects in other databases like this:

ServerName.DatabaseName.SchemaName.ObjectName

So it’s not surprising that when I’ve been reading some questions in Stack Overflow, asking how on earth you can join the list of users in your database, to the list of logins in the master database. And less surprising that the answer normally is that you can’t do that, and you need to connect to each separately. However, that’s not true. You can get to them using external tables. Let me show you how:

Database Scoped Credential

To connect to the master database, you are going to need to logon i.e. you have to provide a username and password. They get stored in a Credential.

Now credentials have been part of SQL Server since 2005 and are just a way of storing an identity and a secret. That could be a username and a password. Until recently, credentials were stored in the master database so they wouldn’t have been any use for this. However, we now have DATABASE SCOPED CREDENTIAL objects. That’s what we need here.

We’ll start by creating one:

CREATE MASTER KEY; -- If you don't already have one
GO

CREATE DATABASE SCOPED CREDENTIAL AccessToMaster
WITH IDENTITY = 'yourmasterlogin', SECRET = 'yourpassword';
GO

You need to create a master encryption key for your database if you don’t already have one. Make sure to change the login and password to one that can access master.

Once this is created, we can use the credential whenever we need to provide details of how to log on to the database.

External Data Source

The next object we need is an EXTERNAL DATA SOURCE. It’s basically a definition of how to connect to another source of data. In this case, it’s the master database:

CREATE EXTERNAL DATA SOURCE MasterDatabase
WITH
(
    TYPE = RDBMS,
    LOCATION = N'yourserver.database.windows.net',
    DATABASE_NAME = N'master',
    CREDENTIAL = AccessToMaster
);
GO

Make sure to put your server name in the LOCATION parameter.

Notice that external data sources are not schema-bound objects i.e. there is no schema name.

External Table

CREATE SCHEMA MasterDB AUTHORIZATION dbo;
GO

CREATE EXTERNAL TABLE MasterDB.sql_logins
(
    [name] sysname, 
    principal_id int, 
    [sid] varbinary(85), 
    [is_disabled] bit 
)
WITH
(
  DATA_SOURCE = MasterDatabase,
  SCHEMA_NAME = 'sys',
  OBJECT_NAME = 'sql_logins'
);
GO

Because I might want to use a number of views from the master database, I’ve created a schema called MasterDB to contain them. I’ve then created an external table MasterDB.sql_logins to point to the sys.sql_logins view in the master database.

External tables can point to views, and notice that I’ve only included the columns that I need. You don’t have to define all the columns from the source view.

And it works!

And if I now query the new external table, all is good as you can see in the main image above.

I hope that helps someone.

2019-09-17