How to create custom user login for Azure SQL Database
When I was trying to create second Azure SQL database on the same shared SQL Server I stuck for a few minutes with a problem that Azure didn’t allow me to create new user (credentials) for this new DB. It turns out that Azure UI does not allow creating users for different databases on the same server.
However, it is logically to have separate credentials for apps per database. Fortunately, we can create users and logins with T-SQL. Here is solution.
Create Azure SQL database with existing user (user with which you have created first DB on the server).
Connect to database with SQL Server Management Studio (SSMS) or any other software you using to manage SQL Server. Azure SQL Server does not allow switching between databases using USE statement, so we can’t create user in one script.
First, we have to create login and user for master database. Open new window for master database:
And run the script:
CREATE LOGIN blog WITH password='xxxxxxx' GO CREATE USER [blog] FOR LOGIN [blog] WITH DEFAULT_SCHEMA=[dbo] GO
Next (and last), open new query window with for desired database:
And run script to create user in the database and assign role to the user:
CREATE USER [blog] FOR LOGIN [blog] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember 'db_owner', 'blog'; GO
Note: Don’t forget to update website connection string on Azure website settings.
Join the discussion