A few weeks ago I posted the steps to create a login account in SQL Server which utilized SQL Server Authentication. While the steps work perfectly for local instances of SQL Server it doesn’t work on when you are operating in Azure SQL Server. I realized this quickly when I decided to use the steps at work with no success. Luckily the differences aren’t major, which this post will go over, so without further delay, here is what you need to do.

The big difference between creating an account on an Azure based SQL Server database versus a local server is under which database you run the steps. When creating the login you’ll run under the master database. Then when creating the user you’ll need to run it once under master and then again under the database the account should be associated with.

-- Execute from master database on the server
CREATE LOGIN [DbLoginUser] WITH PASSWORD = 'Password1'
GO
CREATE USER [DbLoginUser] FOR LOGIN [DbLoginUser] 
GO

The above script creates the login which you should be able to test out immediately after running them. You won’t have access to any of the databases on the server but you should be able to log into the server. The next script will associate the login with the particular database and place it in the db_owner role.

-- Open a new query window in the database you want to create the user
CREATE USER [DbLoginUser] FOR LOGIN [DbLoginUser] WITH DEFAULT_SCHEMA = [dbo]
GO
ALTER ROLE db_owner ADD MEMBER [DbLoginUser]
GO

With the steps completed you should be able to log into the server with the new DbLoginUser account. Additionally, you have full control over the database the user was created under.