Hi @Ashwan ,
Q1. can we configure AD users to access the databases ?
Yes. You can.
Contained Database User can be SQL User or Windows User. We recommend using Windows User. The reasons are two points:
- Kerberos cannot be used if SQL User is used, and there will be problems if a proxy is required.
- You can require the password of Contained SQL User to be a strong password, but these passwords cannot be protected by a password policy.
Use Windows authentication as much as possible to take advantage of richer password policies, Kerberos and other Windows features.
Q2. having database users , how to manage two different passwords with production and non production with same user when we do overnight refresh database to >non production (Testing /dev/pre prod) Environments
ex DB1 , serverprod, user1/<fgbcpassword>
DB1->DB1DEV ,servertest, user1/<pasdvs>
when run refresh production password is not the same as dev env
There is no difference with creating one normal user and passowrd.You can create one same user with different passwords as next and it works well:
--Create a Contained SQL User that is not mapped to the login name
CREATE USER MyContainedUser WITH PASSWORD ='Password01!';
CREATE USER MyContainedUser WITH PASSWORD ='Password01!!!!!!';
Q3. can we maintain database user login details with in instance level ? then password remain different
No. We can't.
Becasue a contained user is created at database level.
More information: contained-database-users-making-your-database-portable, contained-databases-in-sql-server
If the answer is helpful, please click "Accept Answer" and upvote it.