What you have to do in SQL Azure that you take for granted in SQL Server

I have been using the cloud quite extensively and have had to come up with strategies for many things to adapt them to the restrictions on the cloud. I'll be doing a few posts on this as I go.

The first of these was the database setup in SQL Azure.

You usually need to store your data somewhere, I’m not going to go into the merits of Table Storage or SQL Azure, and I’ll cover that separately as the choice comes with penalties each side and needs a whole post about that.

What I do want to cover is the differences in the SQL Script you need to have to setup your databases in the first place in SQL Azure. The first thing you notice is that it’s a lot of extra work, and you notice this pretty early on. Many of the statements you are used to in SQL Server don’t work, or are partially supported, or not supported at all.

Take for example a simple user, you get by default an super user with full rights to the database. This is great to set it all up, but I want my application to have only read access to my database, so I need a new user, NEVER run your application with the privileges of the super user.

I then need to create the user, so I do the click on the database/security/users and say create user, a nice script comes up:

-- =================================================
-- Create User as DBO template for SQL Azure Database
-- =================================================
-- For login , create a user in the database
CREATE USER
FOR LOGIN
WITH DEFAULT_SCHEMA =
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N''
GO

The first thing about SQL azure is there are NO popup wizards to help you, it’s all script, however you should ideally not be using the admin console, I always use script. In SQL Server my script for this would look like so:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
SET XACT_ABORT ON


/*
*********************************************************************************************************
-- TAB SIZE 4
IDENT SIZE 4
---------------------------------------------------------------------------------------------------------
Purpose: Create a new SQL Server Login, assign a default database
and associates the Login with a database read permission


Notes: none.

Revision History:
----------------------------------------------------------------------------------------------------------
| Date | Changed By | Reference | Comments
----------------------------------------------------------------------------------------------------------
| 20100804 | Paul Somers | Create a new login with DB access | Initial release
***********************************************************************************************************
*/
USE master
GO

IF NOT EXISTS (SELECT * FROM syslogins WHERE [NAME] = N'NewReader')
BEGIN
CREATE LOGIN NewReader WITH PASSWORD=N'Password@2',
DEFAULT_DATABASE=GEO1,
DEFAULT_LANGUAGE=us_english,
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
END
USE GEO
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE [name] = N'NewReader')
BEGIN
CREATE USER NewReader FOR LOGIN NewReader
END
USE GEO
GO
EXEC sp_addrolemember N'db_datareader', N'NewReader'
GO

SET QUOTED_IDENTIFIER OFF
GO
SET XACT_ABORT OFF
GO
SET ANSI_NULLS ON
GO

The very next thing you notice about SQL Azure is that if you run this it will not work, whilst this works fine in SQL Server. You get all sorts of errors.

Take the simple, USE statement. USE master

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.

You can’t use it… ? Between databases

Ok, so I need to connect to master run that bit and then run the master part script? Ok so we try that, and then discover that there are MANY parameters that are not allowed, well all of them to be precise.

IF NOT EXISTS (SELECT * FROM syslogins WHERE [NAME] = N'NewReader')
BEGIN
CREATE LOGIN NewReader WITH PASSWORD=N'Password@2'
--DEFAULT_DATABASE=GEO1,
--DEFAULT_LANGUAGE=us_english,
--CHECK_EXPIRATION=ON,
--CHECK_POLICY=ON
END

My traditional way of creating a user will NOT work, or will be extra cumbersome.
It even complains about this statement:

Msg 40530, Level 16, State 1, Line 3
The CREATE LOGIN statement must be the only statement in the batch.

It actually wants you to do a single line statement in the master database:

CREATE LOGIN NewReader WITH PASSWORD=N'Password@2'

That done, I have to switch over to the database I want to give the user permissions to so I run this:

CREATE USER [DataReader]
FOR LOGIN [DataReader]
WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember 'db_datareader', 'DataReader'

To finish it off and make it more robust for repeated deployments I can use:
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DataReader')
DROP USER [DataReader]
GO

So my final steps are:

Connection to master:
CREATE LOGIN NewReader WITH PASSWORD=N'Password@2'

Connection to my specified database where I want to give the user permsission:

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DataReader')
DROP USER [DataReader]
GO
CREATE USER [DataReader]
FOR LOGIN [DataReader]
WITH DEFAULT_SCHEMA = dbo
GO
EXEC sp_addrolemember 'db_datareader', 'DataReader'

It’s a bit of an effort, however if you use a database project in VS you need to keep this in mind.