Creating new logins and users to SQL Azure databases with SQL script

You've just created a new SQL Server database into Azure, and you've set the administrator user and password for it. Surely, you could use this account in your web applications, but that isn't a very great idea. A lot better idea is to create a new user to your database with lesser privileges, such as only the ability to read and write table data.

How would you do this using SQL scripting? You need to set two things: a login (allowing you to connect to the server) and an user with proper user rights to access your database.

Logins are server-wide settings, and thus stored in the "master" system database. To create an SQL Server login, you would execute the following command:

CREATE LOGIN MyUserName WITH PASSWORD = 'someS@cret_Stuff987';

The above command must be run against the "master" database. Then, within your application database, you would first create a user based on the login, and then grant rights (roles) to the user. The built-in roles "db_datareader" and "db_datawriter" are very convenient for this.

Here's an example:

CREATE USER MyUserName FOR LOGIN MyUserName;
ALTER ROLE db_datareader ADD MEMBER MyUserName;
ALTER ROLE db_datawriter ADD MEMBER MyUserName;

Happy cloud development with Azure!