How do I create a new user in a SQL Azure database?

Check out this link for all of the information : https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

First you need to create a login for SQL Azure, its syntax is as follows:

CREATE LOGIN username WITH password='password';

This command needs to run in master db. Only afterwards can you run commands to create a user in the database. The way SQL Azure or SQL Server works is that there is a login created first at the server level and then it is mapped to a user in every database.

HTH

I followed the answers here but when I tried to connect with my new user, I got an error message stating "The server principal 'newuser' is not able to access the database 'master' under the current security context".

I had to also create a new user in the master table to successfully log in with SSMS.

USE [master]

GO

CREATE LOGIN [newuser] WITH PASSWORD=N'blahpw'

GO

CREATE USER [newuser] FOR LOGIN [newuser] WITH DEFAULT_SCHEMA=[dbo]

GO



USE [MyDatabase]

CREATE USER newuser FOR LOGIN newuser WITH DEFAULT_SCHEMA = dbo

GO

EXEC sp_addrolemember N'db_owner', N'newuser'

GO

Edit - Contained User (v12 and later)

As of Sql Azure 12, databases will be created as Contained Databases which will allow users to be created directly in your database, without the need for a server login via master.

Sql (standard) User

CREATE USER [MyUser] WITH PASSWORD = 'Secret';

ALTER ROLE [db_datareader] ADD MEMBER [MyUser]; -- or sp_addrolemember

AAD linked User

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'db_datareader' , N'[email protected]'

AAD linked Group

CREATE USER [SomeGroup] FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'db_datareader' , N'SomeGroup'

NB! when connecting to the database when using a contained user that you must always specify the database in the connection string.



Traditional Server Login - Database User (Pre v 12)

Just to add to @Igorek's answer, you can do the following in Sql Server Management Studio:

Create the new Login on the server
In master (via the Available databases drop down in SSMS - this is because USE master doesn't work in Azure):



create the login:

CREATE LOGIN username WITH password=N'password';

Create the new User in the database
Switch to the actual database (again via the available databases drop down, or a new connection)

CREATE USER username FROM LOGIN username;

(I've assumed that you want the user and logins to tie up as username, but change if this isn't the case.)

Now add the user to the relevant security roles

EXEC sp_addrolemember N'db_owner', N'username'

GO

(Obviously an app user should have less privileges than dbo.)