Login failed for user '<token-identified principal>' Error 18456 while deploying database from on-premises to Azure SQL database Through SSMS

[AmareswarapuBhavani]

Document Enhancement Proposal: Unable to 'Deploy Database to Microsoft Azure SQL Database' from SSMS.

Overview:

Getting below error while deploying on-premises SQL database to Azure SQL database:

Login failed for user '<token-identified principal>' Error 18456

Correction Details:

This guide will allow users to:

Handle the above error and will help to get the activity error details.

Steps:

Reason for the error:

According to the MS document

CREATE DATABASE permissions are necessary. To create a database a login must be either the server admin login (created when the Azure SQL Database logical server was provisioned), the Microsoft Entra admin of the server, a member of the dbmanager database role in master

The user who you provided is not having the necessary permissions to create database. That may be the reason to get above error.

Steps to avoid this error:

If you are using Entra user to login to SQL server, make sure your Entra user is set as server admin, otherwise set server admin as shown below in SQL server:

If the user is from external provider, add dbmanager role to the user in master db using below command:

ALTER ROLE dbmanager ADD MEMBER <user>;

Then you will be able to deploy the database successfully as shown below:

Deployed database:

Note: Use latest version of SSMS i.e. SSMS v20.2, it will work.

Conclusion:

By following this guide, users can avoid the above error.

Reference:

Login failed for user '' Error 18456 On Deploy Database

This Document creation request aims to improve user experience and security by providing clear documentation.

---

title: Create a single database
author: @WilliamDAssafMSFT
ms.author: @wiassaf
ms.reviewer: @MaThoma, @randolphwest
ms.service: azure-sql-database
ms.subservice: deployment-configuration

[PesalaPavan]

@AmareswarapuBhavani
Thanks for your feedback! We will investigate and update as appropriate.

[TPavanBalaji]

@AmareswarapuBhavani
Thank you for bringing this to our attention.
I've delegated this to content author, who will review it and offer their insightful opinions.