Creating New User/Login in SQL Azure

How do I create a new user in a SQL Azure database?

Check out this link for all of the information : https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

First you need to create a login for SQL Azure, its syntax is as follows:

CREATE LOGIN username WITH password='password';

This command needs to run in master db. Only afterwards can you run commands to create a user in the database. The way SQL Azure or SQL Server works is that there is a login created first at the server level and then it is mapped to a user in every database.

HTH

Azure SQL DB - How to create new login, user with restricted access?

Execute the below queries in Master database:

create login [login] with password = ‘<<>>’

Then execute the below queries in actual database:

create user [login] from login [login]

Grant Select ON sch.table1 TO [login] --Read

Replace Select key word with Insert, Update and Delete on the above Read query for write access on the tables

Azure SQL User Login for one database

To give user access to a DB in Azure SQL, you will have to perform one of the following things:

• create a user login in the master DB and then grant it access to appropriate DB
• create a user account within a desired DB

Nonetheless, you can follow the below path. First, create a new login:

CREATE LOGIN <login> WITH PASSWORD = '<password>';

Then create a user inside a DB:

USE <DB-name>
CREATE USER <user> FOR LOGIN <login>;  
GO

See more:

• https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15#examples
• https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

Azure SQL - Create User scoped to a single database

You need to run below query on both the databases (master as well as my_db), and then try with login 'db_login' and password you provided while creating login.

CREATE USER db_login_user FROM LOGIN db_login

Azure SQL is not letting me create a SQL User

Actually, the error is caused by you database login name.

You should using the Login reportslogin to login the database, not the username reportsuser !

We must using the login name to access the Azure SQL database, not the username.

A login is a simple credential for accessing SQL Server. For example,

SQL Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level.

Please reference:

1. Login and SQL User
2. Difference between a User and a Login in SQL Server

Suggestions:

1. create the login and user with the same name:

   USE master
CREATE LOGIN reportslogin WITH password='Xunmi110'

   USE user_database
CREATE USER reportsuser FOR LOGIN reportslogin
ALTER ROLE db_datareader ADD MEMBER reportsuser

2. One login for one user.

Hope this helps.

---

Related Topics