< Back
Export-DbaServerRole
Post
NAME Export-DbaServerRole
SYNOPSIS
Exports server roles to a T-SQL file. Export includes Role creation, object permissions and Schema ownership.
SYNTAX
Export-DbaServerRole [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-InputObject] <System.Object[]>] [[-ScriptingOptionsObject]
<Microsoft.SqlServer.Management.Smo.ScriptingOptions>] [[-ServerRole] <System.String[]>] [[-ExcludeServerRole]
<System.String[]>] [-ExcludeFixedRole <Switch>] [-IncludeRoleMember <Switch>] [[-Path] <String>] [[-FilePath]
<String>] [-Passthru <Switch>] [[-BatchSeparator] <String>] [-NoClobber <Switch>] [-Append <Switch>] [-NoPrefix
<Switch>] [[-Encoding] <String>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Exports Server roles to a T-SQL file. Export includes Role creation, object permissions and Role Members
Applies mostly to SQL Server 2012 or Higher when user defined Server roles were added but can be used on earlier
versions to get role members.
This command is an extension of John Eisbrener's post "Fully Script out a MSSQL Database Role"
Reference: https://dbaeyes.wordpress.com/2013/04/1 ... base-role/
PARAMETERS
-Append [<Switch>]
If this switch is enabled, content will be appended to a file already existing at the path specified by
FilePath. If the file does not exist, it will be created.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
Batch separator for scripting output. Uses the value from configuration Formatting.BatchSeparator by default.
This is normally "GO"
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
-Encoding [<String>]
Specifies the file encoding. The default is UTF8.
Valid values are:
-- ASCII: Uses the encoding for the ASCII (7-bit) character set.
-- BigEndianUnicode: Encodes in UTF-16 format using the big-endian byte order.
-- Byte: Encodes a set of characters into a sequence of bytes.
-- String: Uses the encoding type for a string.
-- Unicode: Encodes in UTF-16 format using the little-endian byte order.
-- UTF7: Encodes in UTF-7 format.
-- UTF8: Encodes in UTF-8 format.
-- Unknown: The encoding type is unknown or invalid. The data can be treated as binary.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeFixedRole [<Switch>]
Filter the fixed server-level roles. As only SQL Server 2012 or higher supports creation of server-level roles
will eliminate all output for earlier versions.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeServerRole [<System.String[]>]
Server-Level role(s) to exclude from results.
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. If left blank then filename based on Instance name, Database
name and date is created.
If more than one database or instance is input then this parameter should normally be blank.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeRoleMember [<Switch>]
Include scripting of role members in script
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<System.Object[]>]
Enables piping from Get-DbaServerRole
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoClobber [<Switch>]
If this switch is enabled, a file already existing at the path specified by Path will not be overwritten. This
takes precedence over Append switch
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoPrefix [<Switch>]
Do not include a Prefix
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<Switch>]
Output script to console only
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.
Will default to Path.DbatoolsExport Configuration entry
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptingOptionsObject [<Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
An SMO Scripting Object that can be used to customize the output - see New-DbaScriptingOption
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ServerRole [<System.String[]>]
Server-Level role(s) to filter results to that role only.
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
<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
NOTES
Tags: Export, Role
Author: Patrick Flynn (@sqllensman)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sql2005
Exports the Server Roles for SQL Server "sql2005" and writes them to the path defined in the ConfigValue
'Path.DbatoolsExport' using a a default name pattern of ServerName-YYYYMMDDhhmmss-serverrole. Uses BatchSeparator
defined by Config 'Formatting.BatchSeparator'
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sql2005 -Path C:\\temp
Exports the Server Roles for SQL Server "sql2005" and writes them to the path "C:\\temp" using a a default name
pattern of ServerName-YYYYMMDDhhmmss-serverrole. Uses BatchSeparator defined by Config 'Formatting.BatchSeparator'
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sqlserver2014a -FilePath C:\\temp\\ServerRoles.sql
Exports the Server Roles for SQL Server sqlserver2014a to the file C:\\temp\\ServerRoles.sql. Overwrites file if
exists
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sqlserver2014a -ServerRole SchemaReader -Passthru
Exports ONLY ServerRole SchemaReader FROM sqlserver2014a and writes script to console
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sqlserver2008 -ExcludeFixedRole -ExcludeServerRole Public
-IncludeRoleMember -FilePath C:\\temp\\ServerRoles.sql -Append -BatchSeparator ''
Exports server roles from sqlserver2008, excludes all roles marked as as FixedRole and Public role. Includes
RoleMembers and writes to file C:\\temp\\ServerRoles.sql, appending to file if it exits. Does not include a
BatchSeparator
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaServerRole -SqlInstance sqlserver2012, sqlserver2014 | Export-DbaServerRole
Exports server roles from sqlserver2012, sqlserver2014 and writes them to the path defined in the ConfigValue
'Path.DbatoolsExport' using a a default name pattern of ServerName-YYYYMMDDhhmmss-serverrole
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Get-DbaServerRole -SqlInstance sqlserver2016 -ExcludeFixedRole -ExcludeServerRole Public |
Export-DbaServerRole -IncludeRoleMember
Exports server roles from sqlserver2016, excludes all roles marked as as FixedRole and Public role. Includes
RoleMembers
RELATED LINKS
https://dbatools.io/Export-DbaServerRole
SYNOPSIS
Exports server roles to a T-SQL file. Export includes Role creation, object permissions and Schema ownership.
SYNTAX
Export-DbaServerRole [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-InputObject] <System.Object[]>] [[-ScriptingOptionsObject]
<Microsoft.SqlServer.Management.Smo.ScriptingOptions>] [[-ServerRole] <System.String[]>] [[-ExcludeServerRole]
<System.String[]>] [-ExcludeFixedRole <Switch>] [-IncludeRoleMember <Switch>] [[-Path] <String>] [[-FilePath]
<String>] [-Passthru <Switch>] [[-BatchSeparator] <String>] [-NoClobber <Switch>] [-Append <Switch>] [-NoPrefix
<Switch>] [[-Encoding] <String>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Exports Server roles to a T-SQL file. Export includes Role creation, object permissions and Role Members
Applies mostly to SQL Server 2012 or Higher when user defined Server roles were added but can be used on earlier
versions to get role members.
This command is an extension of John Eisbrener's post "Fully Script out a MSSQL Database Role"
Reference: https://dbaeyes.wordpress.com/2013/04/1 ... base-role/
PARAMETERS
-Append [<Switch>]
If this switch is enabled, content will be appended to a file already existing at the path specified by
FilePath. If the file does not exist, it will be created.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
Batch separator for scripting output. Uses the value from configuration Formatting.BatchSeparator by default.
This is normally "GO"
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
-Encoding [<String>]
Specifies the file encoding. The default is UTF8.
Valid values are:
-- ASCII: Uses the encoding for the ASCII (7-bit) character set.
-- BigEndianUnicode: Encodes in UTF-16 format using the big-endian byte order.
-- Byte: Encodes a set of characters into a sequence of bytes.
-- String: Uses the encoding type for a string.
-- Unicode: Encodes in UTF-16 format using the little-endian byte order.
-- UTF7: Encodes in UTF-7 format.
-- UTF8: Encodes in UTF-8 format.
-- Unknown: The encoding type is unknown or invalid. The data can be treated as binary.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeFixedRole [<Switch>]
Filter the fixed server-level roles. As only SQL Server 2012 or higher supports creation of server-level roles
will eliminate all output for earlier versions.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeServerRole [<System.String[]>]
Server-Level role(s) to exclude from results.
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. If left blank then filename based on Instance name, Database
name and date is created.
If more than one database or instance is input then this parameter should normally be blank.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeRoleMember [<Switch>]
Include scripting of role members in script
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<System.Object[]>]
Enables piping from Get-DbaServerRole
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoClobber [<Switch>]
If this switch is enabled, a file already existing at the path specified by Path will not be overwritten. This
takes precedence over Append switch
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoPrefix [<Switch>]
Do not include a Prefix
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<Switch>]
Output script to console only
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.
Will default to Path.DbatoolsExport Configuration entry
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptingOptionsObject [<Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
An SMO Scripting Object that can be used to customize the output - see New-DbaScriptingOption
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ServerRole [<System.String[]>]
Server-Level role(s) to filter results to that role only.
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
<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
NOTES
Tags: Export, Role
Author: Patrick Flynn (@sqllensman)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sql2005
Exports the Server Roles for SQL Server "sql2005" and writes them to the path defined in the ConfigValue
'Path.DbatoolsExport' using a a default name pattern of ServerName-YYYYMMDDhhmmss-serverrole. Uses BatchSeparator
defined by Config 'Formatting.BatchSeparator'
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sql2005 -Path C:\\temp
Exports the Server Roles for SQL Server "sql2005" and writes them to the path "C:\\temp" using a a default name
pattern of ServerName-YYYYMMDDhhmmss-serverrole. Uses BatchSeparator defined by Config 'Formatting.BatchSeparator'
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sqlserver2014a -FilePath C:\\temp\\ServerRoles.sql
Exports the Server Roles for SQL Server sqlserver2014a to the file C:\\temp\\ServerRoles.sql. Overwrites file if
exists
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sqlserver2014a -ServerRole SchemaReader -Passthru
Exports ONLY ServerRole SchemaReader FROM sqlserver2014a and writes script to console
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Export-DbaServerRole -SqlInstance sqlserver2008 -ExcludeFixedRole -ExcludeServerRole Public
-IncludeRoleMember -FilePath C:\\temp\\ServerRoles.sql -Append -BatchSeparator ''
Exports server roles from sqlserver2008, excludes all roles marked as as FixedRole and Public role. Includes
RoleMembers and writes to file C:\\temp\\ServerRoles.sql, appending to file if it exits. Does not include a
BatchSeparator
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaServerRole -SqlInstance sqlserver2012, sqlserver2014 | Export-DbaServerRole
Exports server roles from sqlserver2012, sqlserver2014 and writes them to the path defined in the ConfigValue
'Path.DbatoolsExport' using a a default name pattern of ServerName-YYYYMMDDhhmmss-serverrole
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Get-DbaServerRole -SqlInstance sqlserver2016 -ExcludeFixedRole -ExcludeServerRole Public |
Export-DbaServerRole -IncludeRoleMember
Exports server roles from sqlserver2016, excludes all roles marked as as FixedRole and Public role. Includes
RoleMembers
RELATED LINKS
https://dbatools.io/Export-DbaServerRole