< Back
Export-DbaUser
Post
NAME Export-DbaUser
SYNOPSIS
Exports users creation and its permissions to a T-SQL file or host.
SYNTAX
Export-DbaUser [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [[-InputObject]
<Microsoft.SqlServer.Management.Smo.Database[]>] [[-SqlCredential] <Pscredential>] [[-Database] <System.String[]>]
[[-ExcludeDatabase] <System.String[]>] [[-User] <System.String[]>] [[-DestinationVersion] <String>] [[-Path]
<String>] [[-FilePath] <String>] [-NoClobber <Switch>] [-Append <Switch>] [-Passthru <Switch>] [-EnableException
<Switch>] [[-ScriptingOptionsObject] <Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
[-ExcludeGoBatchSeparator <Switch>] [<CommonParameters>]
DESCRIPTION
Exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to
role(s), database level permissions, object level permissions.
PARAMETERS
-Append [<Switch>]
Append to file
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.String[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all InputObject will
be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationVersion [<String>]
To say to which version the script should be generated. If not specified will use database compatibility level
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.String[]>]
The database(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeGoBatchSeparator [<Switch>]
If specified, will NOT script the 'GO' batch separator.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilePath [<String>]
Specifies the full file path of the output file.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]
Allows database objects to be piped in from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoClobber [<Switch>]
Do not overwrite file
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<Switch>]
Output script to console, useful with | clip
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptingOptionsObject [<Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
A Microsoft.SqlServer.Management.Smo.ScriptingOptions object with the options that you want to use to generate
the t-sql script.
You can use the NEw-DbaScriptingOption to generate it.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. SQL Server 2000 and above supported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-User [<System.String[]>]
Export only the specified database user(s). If not specified will export all users from the database(s)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
System.String
NOTES
Tags: User, Export
Author: Claudio Silva (@ClaudioESSilva)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sql2005 -Path C:\\temp\\sql2005-users.sql
Exports SQL for the users in server "sql2005" and writes them to the file "C:\\temp\\sql2005-users.sql"
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2014a $scred -Path C:\\temp\\users.sql -Append
Authenticates to sqlserver2014a using SQL Authentication. Exports all users to C:\\temp\\users.sql, and appends to
the file if it exists. If not, the file will be created.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2014a -User User1, User2 -Path C:\\temp\\users.sql
Exports ONLY users User1 and User2 from sqlserver2014a to the file C:\\temp\\users.sql
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2008 -User User1 -Path C:\\temp\\users.sql -DestinationVersion
SQLServer2016
Exports user User1 from sqlserver2008 to the file C:\\temp\\users.sql with syntax to run on SQL Server 2016
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2008 -Database db1,db2 -Path C:\\temp\\users.sql
Exports ONLY users from db1 and db2 database on sqlserver2008 server, to the C:\\temp\\users.sql file.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>$options = New-DbaScriptingOption
PS C:\\> $options.ScriptDrops = $false
PS C:\\> $options.WithDependencies = $true
PS C:\\> Export-DbaUser -SqlInstance sqlserver2008 -Database db1,db2 -Path C:\\temp\\users.sql
-ScriptingOptionsObject $options
Exports ONLY users from db1 and db2 database on sqlserver2008 server, to the C:\\temp\\users.sql file.
It will not script drops but will script dependencies.
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2008 -Database db1,db2 -Path C:\\temp\\users.sql -ExcludeGoBatchSeparator
Exports ONLY users from db1 and db2 database on sqlserver2008 server, to the C:\\temp\\users.sql file without the
'GO' batch separator.
RELATED LINKS
https://dbatools.io/Export-DbaUser
SYNOPSIS
Exports users creation and its permissions to a T-SQL file or host.
SYNTAX
Export-DbaUser [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [[-InputObject]
<Microsoft.SqlServer.Management.Smo.Database[]>] [[-SqlCredential] <Pscredential>] [[-Database] <System.String[]>]
[[-ExcludeDatabase] <System.String[]>] [[-User] <System.String[]>] [[-DestinationVersion] <String>] [[-Path]
<String>] [[-FilePath] <String>] [-NoClobber <Switch>] [-Append <Switch>] [-Passthru <Switch>] [-EnableException
<Switch>] [[-ScriptingOptionsObject] <Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
[-ExcludeGoBatchSeparator <Switch>] [<CommonParameters>]
DESCRIPTION
Exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to
role(s), database level permissions, object level permissions.
PARAMETERS
-Append [<Switch>]
Append to file
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.String[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all InputObject will
be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationVersion [<String>]
To say to which version the script should be generated. If not specified will use database compatibility level
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.String[]>]
The database(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeGoBatchSeparator [<Switch>]
If specified, will NOT script the 'GO' batch separator.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilePath [<String>]
Specifies the full file path of the output file.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]
Allows database objects to be piped in from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoClobber [<Switch>]
Do not overwrite file
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<Switch>]
Output script to console, useful with | clip
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptingOptionsObject [<Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
A Microsoft.SqlServer.Management.Smo.ScriptingOptions object with the options that you want to use to generate
the t-sql script.
You can use the NEw-DbaScriptingOption to generate it.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. SQL Server 2000 and above supported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-User [<System.String[]>]
Export only the specified database user(s). If not specified will export all users from the database(s)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
System.String
NOTES
Tags: User, Export
Author: Claudio Silva (@ClaudioESSilva)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sql2005 -Path C:\\temp\\sql2005-users.sql
Exports SQL for the users in server "sql2005" and writes them to the file "C:\\temp\\sql2005-users.sql"
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2014a $scred -Path C:\\temp\\users.sql -Append
Authenticates to sqlserver2014a using SQL Authentication. Exports all users to C:\\temp\\users.sql, and appends to
the file if it exists. If not, the file will be created.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2014a -User User1, User2 -Path C:\\temp\\users.sql
Exports ONLY users User1 and User2 from sqlserver2014a to the file C:\\temp\\users.sql
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2008 -User User1 -Path C:\\temp\\users.sql -DestinationVersion
SQLServer2016
Exports user User1 from sqlserver2008 to the file C:\\temp\\users.sql with syntax to run on SQL Server 2016
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2008 -Database db1,db2 -Path C:\\temp\\users.sql
Exports ONLY users from db1 and db2 database on sqlserver2008 server, to the C:\\temp\\users.sql file.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>$options = New-DbaScriptingOption
PS C:\\> $options.ScriptDrops = $false
PS C:\\> $options.WithDependencies = $true
PS C:\\> Export-DbaUser -SqlInstance sqlserver2008 -Database db1,db2 -Path C:\\temp\\users.sql
-ScriptingOptionsObject $options
Exports ONLY users from db1 and db2 database on sqlserver2008 server, to the C:\\temp\\users.sql file.
It will not script drops but will script dependencies.
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Export-DbaUser -SqlInstance sqlserver2008 -Database db1,db2 -Path C:\\temp\\users.sql -ExcludeGoBatchSeparator
Exports ONLY users from db1 and db2 database on sqlserver2008 server, to the C:\\temp\\users.sql file without the
'GO' batch separator.
RELATED LINKS
https://dbatools.io/Export-DbaUser