How to create a contained database and user in azure sql

Question

I am trying to create a contained user for just one database in Azure SQL Server,

I have tried using the sp_configure keyword, it says it is not available in the version of the SQL Server I am using.

Also, I used the Alter database statement, I got the error below:

ALTER DATABASE statement failed; this functionality is not available in the current edition of SQL Server.

Please, how can I solve this problem???

Answer 1

You do not need to run the ALTER DATABASE ... SET CONTAINMENT command on Azure SQL DBs to accept contained users - it is already enabled by default. You simply need to create the user with just a login and password. A simple example of a contained user with password:

CREATE USER yourUser WITH PASSWORD = 'yourPassword';

See the official documentation for more examples:

• https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15#e-creating-a-contained-database-user-with-password
• https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15#f-creating-a-contained-database-user-for-a-domain-login

Answer 2

sp_configure is not supported in Azure SQL database, even use the Alter database:

In Azure SQL database, login is used to login the Azure SQL server, user is to connect to the database. User is database level, and login is server level.

Create login in master DB(( Login must be created in master DB)):

CREATE LOGIN AbolrousHazem   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

Then we can create user in user DB( create the database contained user in user DB):

CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;  
GO

For more details, please ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage