Azure SQL - Newly Created SQL user does not allow me to access database.

Question

Hello,

In Azure SQL - Master database I have created new SQL user that should be used to access all the database on the same server.

but while i am trying to access with that user it shows below error :

.

Have follow below sql queries

-CREATE LOGIN testlogin WITH password='...';

--CREATE USER testloginUser FOR LOGIN testlogin
--WITH DEFAULT_SCHEMA = master;

--alter role [dbmanager] add member testloginUser;

Answer 1

Good day Atulkumar,

You can try this

-- in the master database we need to create LOGIN  
create login [MyLogin] with password = 'Not1My2Real3Password'  
  
-- in teach database including the the master database, we need to create a USER, so we will be able to use the it by the LOGIN  
-- This mean that in each database we add this the person will be able to connect, but still will have no permissions to do anything - keep reading  
-- My guess is that you only created a USER in the master database but a USER is a databases level entity  
create user [MyUser] from login [MyLogin];  

Now we are moving to the permissions which you want to give your LOGIN/User. Here are some options for example

-- if you want the user to be able to create databases, then in the master database  
exec sp_addRoleMember 'dbmanager', 'MyUser';  
  
-- if you want the user to be able to create other LOGIN, then in the master database  
exec sp_addRoleMember 'loginmanager', 'MyUser'  
  
-- in each individual database, that you want to give the USER full control in the database level, grant him db_owner  
exec sp_addRoleMember 'db_owner', 'MyUser';  

Note! Give the USER only the permission that he need and nothing more! The above is only a sample of a few permissions. Use GRANT to give permissions on specific entities in the database and don't make each USER a database owner.

For more information you should check the following document:

https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

Answer 2

Hi @Atulkumar Thummar ,

Check to see if your user is mapped to the DB you are trying to log into.

in SSMS, expand Security->Logins, right-click the appropriate login and choose Login Properties
select User Mapping, from here you can select additional database you want to log into with this user

https://stackoverflow.com/questions/19009488/the-server-principal-is-not-able-to-access-the-database-under-the-current-securi