New-DbaDbUser

Synopsis

Creates database users with support for SQL logins, contained users, and Azure AD authentication.

Description

Creates database users across one or more databases, supporting multiple authentication types including traditional SQL login mapping, contained users with passwords, and Azure Active Directory external provider authentication. This command handles the common DBA task of provisioning database access without requiring manual T-SQL scripts for each database. You can create users mapped to existing SQL logins, standalone contained users for partially contained databases, or Azure AD users for cloud environments. The function automatically validates that specified logins and schemas exist before attempting user creation.

Syntax

New-DbaDbUser
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-IncludeSystem]
    -User <String>
    -ExternalProvider
    [-DefaultSchema <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

New-DbaDbUser
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-IncludeSystem]
    -User <String>
    -SecurePassword <SecureString>
    [-DefaultSchema <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

New-DbaDbUser
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-IncludeSystem]
    -User <String>
    [-DefaultSchema <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

New-DbaDbUser
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-IncludeSystem]
    [-User <String>]
    -Login <String>
    [-DefaultSchema <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaDbUser -SqlInstance sqlserver2014 -Database DB1 -Login user1

Creates a new sql user named user1 for the login user1 in the database DB1.

Example: 2

PS C:\> New-DbaDbUser -SqlInstance sqlserver2014 -Database DB1 -User user1

Creates a new sql user named user1 without login in the database DB1.

Example: 3

PS C:\> New-DbaDbUser -SqlInstance sqlserver2014 -Database DB1 -User user1 -Login login1

Creates a new sql user named user1 for the login login1 in the database DB1.

Example: 4

PS C:\> New-DbaDbUser -SqlInstance sqlserver2014 -Database DB1 -User user1 -Login Login1 -DefaultSchema schema1

Creates a new sql user named user1 for the login login1 in the database DB1 and specifies the default schema to be schema1.

Example: 5

PS C:\> New-DbaDbUser -SqlInstance sqlserver2014 -Database DB1 -User "claudio@********.onmicrosoft.com" -ExternalProvider

Creates a new sql user named ‘claudio@********.onmicrosoft.com’ mapped to Azure Active Directory (AAD) in the database DB1.

Example: 6

PS C:\> New-DbaDbUser -SqlInstance sqlserver2014 -Database DB1 -Username user1 -Password (ConvertTo-SecureString -String "DBATools" -AsPlainText -Force)

Creates a new contained sql user named user1 in the database DB1 with the password specified.

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

-User

Sets the name of the database user to be created. Required for contained users, external provider users, and users without logins.
If not specified when using -Login, the user name will match the login name.

-Login

Maps the database user to an existing SQL Server login for authentication.
The login must already exist on the instance before creating the user.

-SecurePassword

If we need to pass a password to the command, we always use the type securestring and name the parameter SecurePassword. Here we only use the alias for backwords compatibility.

-ExternalProvider

Creates a user for Azure Active Directory authentication in Azure SQL databases or SQL Server with AAD integration.
The User parameter should contain the full AAD principal name ([email protected] or groupname).

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to create the user in. Accepts multiple database names separated by commas.
If not specified, the user will be created in all user databases on the instance.

-ExcludeDatabase

Excludes specific databases from user creation when processing all databases on an instance.
Use this to skip databases where you don’t want the user created, such as read-only or archived databases.

-IncludeSystem

Creates the user in system databases (master, model, msdb, tempdb) in addition to user databases.
Typically used when creating maintenance or administrative users that need access to system databases.

-DefaultSchema

Sets the default schema that will be used when the user creates objects without specifying a schema.
Defaults to ‘dbo’ if not specified. The schema must already exist in the target database.

-Force

Drops and recreates the user if it already exists in the database.
Use this when you need to reset a user’s properties or when automation scripts need to ensure a clean user state.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

Outputs

Microsoft.SqlServer.Management.Smo.User

Returns one User object for each user created, with one object per database when creating users in multiple databases.

Default display properties (via Select-DefaultView):

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance)
• Database: The database name where the user was created
• Name: The name of the created database user
• LoginType: Type of login this user is based on (WindowsLogin, SqlLogin, Certificate, AsymmetricKey, etc.)
• Login: The login name the user is mapped to (null for contained users or external provider users)
• AuthenticationType: Authentication type used (SqlLogin for SQL authenticated, WindowsLogin, Certificate, AsymmetricKey, ExternalUser for AAD, None for NoLogin users)
• DefaultSchema: The default schema for this user (configured via -DefaultSchema parameter, defaults to ‘dbo’)

*Additional properties available from SMO User object (via Select-Object ):

• ID: Object ID of the user
• Owner: Principal that owns this user
• State: Current object state (Existing, Creating, Pending, Dropping)
• Urn: Unified Resource Name for the object
• Properties: Collection of object properties

Conditional properties based on user type:

• When created with -Login parameter: User has a login property set to the mapped login
• When created with -SecurePassword parameter: User is a contained database user with no login mapping
• When created with -ExternalProvider switch: User is an Azure AD external provider user with AuthenticationType = ExternalUser Output quantity: One object per user per database. When creating the same user in multiple databases (via -Database parameter accepting multiple values), one object is returned per database with the same user name but different Database property values.