March Madness – SQL Azure – sys.sql_logins

It’s that time of year again folks and March Madness is upon us. Last year I did a blog series on the system tables found within SQL Server. This year I am leaving the earth-bound editions of SQL Server behind and will focus on SQL Azure.

If you are new to SQL Azure then I will point you to this resource list by Buck Woody (blog | @buckwoody). If you are not new, but simply cloud-curious, then join me for the next 19 days as we journey into the cloud and back again. Also, Glenn Berry (blog | @GlennAlanBerry)  has a list of handy SQL Azure queries, and between his blog and Buck Woody I have compiled the items that will follow for the next 19 days.

How To Get Started With SQL Azure

It really isn’t that complex. Here’s what you do:

1. Go to http://windows.azure.com
2. Either create an account or use your Live Microsoft account to sign in
3. You can select the option to do a free 90 day trial
4. Create a new SQL Azure server
5. Create a new database
6. Get the connection string details and use them inside of SSMS to connect to SQL Azure

That’s really all there is to it, so simple even I could do it.

What Will This Blog Series Cover?

I am going to focus on the currently available system tables, views, functions, and stored procedures you may find most useful when working with Azure. Keep in mind that SQL Azure changes frequently. So frequently in fact that you should bookmark this page on MSDN that details what is new with the current build of SQL Azure.

Last year I started with the syslogins table. Well, that table doesn’t exist in SQL Azure, but sys.sql_logins does. But wait…before I even go there…how do I know what is available inside of SQL Azure? Well, I could go over to MSDN and poke around the documentation all day. Or, I could take 10 minutes to open up an Azure account and just run the following query:

SELECT name, type_desc
 FROM sys.all_objects

You should understand that you only get to connect to one database at a time in SQL Azure. The exception is when you connect inside of SSMS initially to the master database then you *can* toggle to a user database in the dropdown inside of SSMS, but after that you are stuck in that user database. I created a quick video to help show this better.

You need to open a new connection in order to query a new database, including master. Why am I telling you this? Because you won’t be able to run this from a user database:

SELECT name, type_desc
 FROM master.sys.all_objects

You will get back this error message:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in ‘master.sys.all_objects’ is not supported in this version of SQL Server.

I found that master has 242 objects listed in the sys.all_objects view but only 210 are returned against a user database. So, which ones are missing? Well, you’ll have to go find out on your own. I will give you one interesting item…

It would appear that the master database in SQL Azure has two views named sys.columns and sys.COLUMNS. Why the two? I have no idea. From what I can tell they return the exact same sets of data. So where is the difference? It’s all about the schema. If you run the following:

SELECT name, schema_id, type_desc
 FROM master.sys.all_objects
 WHERE name = 'COLUMNS'

You will see that there are two different schema ids in play, a 3 (for the INFORMATION_SCHEMA schema) and a 4 (for the sys schema).

Of course, now I am curious to know about the collation of this instance, but we will save that for a later post. Let’s get back to those logins. To return a list of all logins that exist on your SQL Azure instance you simply run:

SELECT *
 FROM sys.sql_logins

You can use this view to gather details periodically to make certain that the logins to your SQL Azure instance are not being add/removed/modified, for example.

OK, that’s enough for day one. Tomorrow we will peek under the covers of a user database.