Copying Databases and Creating Users and Logins for SQL Azure

When you create a new SQL Azure server you will get an empty server, and you will define a server level principal user.  The server level principal (SLP) user is basically a super-user for that server, he can create new databases and new users, the equivalent of sa on an on-premise SQL Server.

The SLP will have access to any database that is created, but strangely, and importantly NOT any database that has been created as a copy of another.  I guess this is because Copy Database is an Azure only feature, hence the fact that the SLP doesn’t have access (I guess this is an oversight rather than a deliberate security measure, I may be wrong – it’s happened before).

So the fact that the SLP cannot access Copy databases is important, because if you plan to restore a copy, or use a copy in the event of a database disaster, you’ll need to be able to create a new user who can access the Copy DB.  In fact from a security point of view, you would be far better creating a new database owner for every database you plan to run on your server.

This post outlines the steps that are required to do this

1.    Copy Database

This copy command must be run from the master database.  It will create an exact copy of the original database.

CREATE DATABASE Iain_TEST_Copy
AS COPY OF Iain_TEST

2. Create Login and User

No user will be able to connect to your copy db, so you will need to create a new login and user. Now this was initially the cause of much head-scratching for me, because in SQL Server land, Logins and Users are 2 different things.

A Login is required to login to the server, and a user is required to access the database.

So again from the master db run the following command to create your login:

CREATE LOGIN iainlogin WITH password='password1!';

Then create your user

CREATE USER iain FROM LOGIN iainlogin

Now connect to your copy db, in this example Iain_Test_Copy and create your new user in this database also

CREATE USER iain FROM LOGIN iainlogin

3. Grant Permission

So we now have a user on your copy database, but he can’t do anything until he has the relevant permissions. You can grant permissions to your user as “granularly” as you wish. For simplicity I’m going to make our new user db_owner, so he can access the db. So again from your new DB

EXEC sp_addrolemember 'db_owner', iain;

4. Connecting to your new DB

So if your login is called iainlogin and your user is called iain, what do you think your connection string User Id would be?

Data Source=sqlazure.database.windows.net;Initial Catalog=Iain_TEST_Copy;Persist Security Info=True;User ID=; Password=password

If you said my User ID is iain, so that’s what I’ll put in my connection string, your application of common-sense would be completely wrong. Hilariously and completely non-obviously you actually put in your login. So your connection string would look like this

Data Source=sqlazure.database.windows.net;Initial Catalog=Iain_TEST_Copy;Persist Security Info=True;User ID=iainlogin@sqlazure; Password=password

You should now be able to connect to your new database

Conclusions

So there we have it, easy when you know the right answer, and understand the subtle but very important difference between logins and users. So knowing this, I’d actually say for simplicity that you should create your login and user with the same name, ie

CREATE LOGIN iain WITH password='password1!';
CREATE USER iain FROM LOGIN iain

That way it’s somewhat easier to remember and deal with.

Useful query

I wrote this query so you can quickly see the users on a DB and the roles they have assigned:

select r.member_principal_id, u.name, u2.name
  from sys.database_role_members r,
       sys.sysusers u,
       sys.sysusers u2
 where r.member_principal_id = u.uid
   and r.role_principal_id = u2.uid

Useful links

Copy SQL Azure databases
Create users within SQL Azure databases
Users and Logins

Advertisement