Product:

Microsoft SQL Azure

Issue:

How create a user in SQL Azure, as you do not have a GUI to use in SSMS.

Solution:

Connect to Azure SQL with SSMS as administrator.

Connect to your user database, and enter below SQL statement to create user Roger and give it datareader rights:

CREATE User Roger
WITH PASSWORD = 'Password!'

ALTER ROLE db_datareader ADD member Roger

This user only exist in the database selected.

To create a user that will need access to more than one database, select the master database and enter below SQL:

CREATE Login Roger
WITH PASSWORD = 'Password!' 

CREATE USER Roger FOR LOGIN Roger

(You need to create the user in master database to, so he can login when master is default database)

Then switch to your user database and enter this SQL statement:

CREATE USER Roger FOR LOGIN Roger

ALTER ROLE db_datareader ADD member Roger

 

Best is to use a AD group instead, and set that SQL login to have the needed rights in the database.

 

More Information:

https://stackoverflow.com/questions/55230724/set-default-database-to-user-sql-server-azure 

https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver16 

— =======================================================================================
— Create User as DBO template for Azure SQL Database and Azure Synapse Analytics Database
— =======================================================================================
— For login login_name, create a user in the database
CREATE USER <user_name, sysname, user_name>
FOR LOGIN <login_name, sysname, login_name>
WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

— =======================================================================================
— Create Azure Active Directory User for Azure SQL Database and Azure Synapse Analytics Database
— =======================================================================================
— For login <login_name, sysname, login_name>, create a user in the database
— CREATE USER <Azure_Active_Directory_Principal_User, sysname, user_name>
— [ { FOR | FROM } LOGIN <Azure_Active_Directory_Principal_Login, sysname, login_name> ]
— | FROM EXTERNAL PROVIDER
— [ WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo> ]
— GO

— Add user to the database owner role
EXEC sp_addrolemember N’db_owner’, N'<user_name, sysname, user_name>’
GO