Create containted database users DB or Windows

Question

Hi my version is 2016 EE SP2 edition and creating a contained database.
Question in related to create users. I understand more encurage to create database users rather AD users.

Q1. can we configure AD users to access the databases ?
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>

refresh to
DB1->DB1DEV ,servertest, user1/<pasdvs>

when run refresh production password is not the same as dev env

Q3. can we maintain database user login details with in instance level ? then password remain different

thanks

Answer 1

Q1: Yes, a contained user can be a windows user ("point to a Windows account"). (The other type of contained user is an SQL user with password.)

Q2: Obviously an SQL user can only have one password. So your option is to change the password after the resfresh.

Q3: No, a contained user doesn't have a login, That is the point of having them in the first place.

Answer 2

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:

1. Kerberos cannot be used if SQL User is used, and there will be problems if a proxy is required.
2. 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>
refresh to
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  
use contdb  
CREATE USER MyContainedUser WITH PASSWORD ='Password01!';  
GO  
  
use contdb1  
CREATE USER MyContainedUser WITH PASSWORD ='Password01!!!!!!';  
GO  

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

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @Ashwan ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.