We found that with contained databases / contained users you must specify:
GRANT CONNECT TO [YOUR_USER]
Otherwise CONNECT
seems to be revoked by default. Once we made the above change, we could access the database.
Reading you comment I see now what you are looking for.
Here is how you can do this.
Create 2 empty database:
USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO
USE [master]
GO
CREATE DATABASE [TestPerm2]
GO
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO
Create 2 login
USE [master]
GO
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Create a user in master using one of the login
USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO
Using SSMS connect with user testPerm2
using default setting. You will be able to see all database ( I know you do not want this, but hang on)
Create a user with testPerm1
in testPerm1
database that we created earlier.
USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO
Now using SSMS connect to the server by changing default database, this user will only see testPerm1
but not testPerm2
.
Best Answer
This is currently not supported and you cannot use Must_Chance, Check_Policy or Check_expiration as of now.
You can vote for this in the forum :
https://feedback.azure.com/forums/217321-sql-database/suggestions/10491111-azure-sql-database-password-force-change-on-first