Add a user to a sql database in azure without syncing with entra id; where can i find ssms

Hi @Jeanne Howard ,

Thank you for reaching out to the Microsoft Q&A forum. 

If you do not want to use Microsoft Entra ID, you can create contained database users directly in Azure SQL Database and authenticate them using a username and password specific to that database.

Since you have owner/admin access, the quickest option is to use the Query Editor in the Azure Portal (if enabled) or connect with tools such as Azure Data Studio, sqlcmd, or SQL Server Management Studio (SSMS).

You can create a user and assign the required permissions using T-SQL:

CREATE USER [username] WITH PASSWORD = 'StrongPassword!';
ALTER ROLE db_datareader ADD MEMBER [username];
ALTER ROLE db_datawriter ADD MEMBER [username];
ALTER ROLE db_ddladmin ADD MEMBER [username];

Role descriptions:

• db_datareader: Read data from tables and views.
• db_datawriter: Insert, update, and delete data.
• db_ddladmin: Create and modify database objects such as tables, views, stored procedures, and functions.
• db_owner: Full control of the database (use only when absolutely necessary).

For users who need to edit data and participate in database development for web applications, the combination of db_datareader, db_datawriter, and db_ddladmin is typically sufficient and follows the principle of least privilege.

Regarding SSMS, it is a separate client tool and is not automatically available through the Azure Portal. You can download and install the latest version of SSMS locally if you prefer a graphical management experience.

If you encounter any errors while creating the user or assigning roles, please share the exact error message and we can help troubleshoot further.

You may try:

Azure SQL Database then Portal Query editor (preview), in the database's left menu. Fastest given your owner access.

Any platform then Azure Data Studio or sqlcmd.

Inside a database (run via any of the tools above):

CREATE USER [username] WITH PASSWORD = 'StrongPassword!';  -- Azure SQL DB, contained user
ALTER ROLE db_datawriter ADD MEMBER [username];  -- edit data
ALTER ROLE db_datareader ADD MEMBER [username];  -- read data
ALTER ROLE db_ddladmin  ADD MEMBER [username];   -- create/alter tables, procs (schema)

If you don't have SSMS installed, I would recommend that you install and download it. Without SSMS, or some other query tool, administering a database is difficult. But there is a query editor in the Azure portal.

There is no predefined role db_writer, but there is a predefined db_datawriter. Membership in that role gives the user rights to insert, update and delete data in tables. However, you talk about other users being able to create web apps. The web apps themselves are created elsewhere, but possibly web app developers wants to create tables and stored procedures. Permission to do that does not follow with db_datawriter.

To that end, I recommend that you do:

CREATE ROLE my_ddladmin
ALTER MEMBER db_ddladmin ADD MEMBER my_ddladmin
DENY ALTER ANY DATABASE DDL TRIGGER TO my_ddladmin

Then you add developers to the role my_ddladmin.

That is, the predefined role db_ddladmin gives users rights to create stored procedures, tables etc. Unfortunately, it also gives them permission to deal with DDL triggers, something which should reserved for the database owner. This is why I recommend that you to create your own role.

Note that membership in db_ddladmin, does not give permission to change data in tables, so developers also need membership in db_ddladmin.