How to create a login for a SQL Azure database
I tend to use SQL Azure databases to power most of the Umbraco websites I build, and for each database I set up a new user account (security is king etc!).
However on SQL Azure you have to script them because it doesn’t let you set up user accounts through the Management Studio interface. This means that every time I set up a new database I always have to google how to script the user logins, as I always forget!
So here’s how to do it so that my future self can just come back here and read it in the comfort of my own blog…
Step 1 – connect to your SQL Azure DB Server either through SQL Management Studio or through the Azure Management portal.
Step 2 – connect to the Master database and execute this query:
CREATE LOGIN ['yourLoginName'] WITH password='yourReallyStrongPassword';
Step 3 – connect to your new database that you want to set up the login for, and execute this query:
CREATE USER [yourLoginName] FROM LOGIN yourLoginName;
Step 4 – staying connected to your new database, execute this stored procedure:
EXEC sp_addrolemember 'db_owner', 'yourLoginName';
(you can add any role you want in this step, I’m just using db_owner for illustration…)
And that’s it – so simple that you will probably forget it the next time you want to create a user login for your SQL Azure database! 🙂