Create containted database users DB or Windows

Ashwan 441 Reputation points
2020-09-25T08:14:04.917+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,588 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. tibor_karaszi@hotmail.com 4,291 Reputation points MVP
    2020-09-25T09:17:22.647+00:00

    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.

    No comments

  2. Miamiao-MSFT 4,216 Reputation points
    2020-09-28T03:37:19.183+00:00

    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  
    

    28496-20200928differentdbdifferentpassword.jpg

    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.

    No comments

  3. Miamiao-MSFT 4,216 Reputation points
    2020-09-29T01:34:39.543+00:00

    Hi @Ashwan ,

    Is the reply helpful?

    BR,
    Mia


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

    No comments