| Author | Patrick Flynn (@sqllensman) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Export-DbaServerRole on GitHub.
Want to see the Bill Of Health for this command? Check out Export-DbaServerRole.
Generates T-SQL scripts for server-level roles including permissions and memberships
Creates complete T-SQL scripts that can recreate server-level roles along with their permissions and memberships on another instance. This eliminates the need to manually recreate security configurations during server migrations or disaster recovery scenarios. The function queries sys.server_permissions to capture all role permissions (GRANT, DENY, REVOKE) and generates the appropriate T-SQL statements for role creation and member assignments.
Primarily targets SQL Server 2012 and higher where user-defined server roles were introduced, but works on earlier versions to script role memberships for built-in roles.
This command extends John Eisbrener's post "Fully Script out a MSSQL Database Role"
Reference: https://dbaeyes.wordpress.com/2013/04/19/fully-script-out-a-mssql-database-role/
Export-DbaServerRole
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-InputObject] <Object[]>]
[[-ScriptingOptionsObject] <ScriptingOptions>]
[[-ServerRole] <String[]>]
[[-ExcludeServerRole] <String[]>]
[-ExcludeFixedRole]
[-IncludeRoleMember]
[[-Path] <String>]
[[-FilePath] <String>]
[-Passthru]
[[-BatchSeparator] <String>]
[-NoClobber]
[-Append]
[-NoPrefix]
[[-Encoding] <String>]
[-EnableException]
[<CommonParameters>]
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'
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'
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
PS C:\> Export-DbaServerRole -SqlInstance sqlserver2014a -ServerRole SchemaReader -Passthru
Exports ONLY ServerRole SchemaReader FROM sqlserver2014a and writes script to console
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
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
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
The target SQL Server instance or instances. SQL Server 2000 and above supported.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Accepts server role objects from Get-DbaServerRole for pipeline processing. Use this when you need to filter roles first with Get-DbaServerRole before exporting.
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
Provides custom SMO scripting options to control the generated T-SQL output format. Use New-DbaScriptingOption to create custom options when you need specific formatting requirements like excluding
object owners or database context.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies which server-level roles to export by name. Useful when you only need to script specific custom roles instead of all roles on the instance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Excludes specific server-level roles from the export by name. Use this to skip problematic roles or roles you don't want to migrate to the target instance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Excludes built-in server roles like sysadmin, serveradmin, and dbcreator from the export. Use this when migrating between instances where you only want to transfer custom user-defined roles. On SQL
Server 2008/2008R2, this will exclude all roles since user-defined server roles weren't supported.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Includes ALTER SERVER ROLE statements to add current role members to the exported script. Essential when you need to recreate both the roles and their membership assignments on the target instance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Specifies the directory where script files will be saved. Defaults to the Path.DbatoolsExport configuration setting. Use this when you want to organize exports in a specific folder structure for your
deployment process.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | (Get-DbatoolsConfigValue -FullName 'Path.DbatoolsExport') |
Specifies the complete file path for the exported script. When blank, creates timestamped files using the instance name. Use this when you need consistent file naming for deployment pipelines or when
exporting from a single instance.
| Alias | OutFile,FileName |
| Required | False |
| Pipeline | false |
| Default Value |
Displays the generated T-SQL script in the console instead of saving to file. Perfect for quick review of the script or when you need to copy-paste the output directly into SSMS.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Sets the batch separator used between T-SQL statements in the output. Defaults to the configured value, typically 'GO'. Change this when deploying to tools that use different batch separators or set
to empty string to remove separators entirely.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | (Get-DbatoolsConfigValue -FullName 'Formatting.BatchSeparator') |
Prevents overwriting existing files at the target location. Use this as a safety measure when running automated exports to avoid accidentally replacing important deployment scripts.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Adds the exported script to an existing file instead of overwriting it. Useful when building comprehensive deployment scripts that combine multiple exports into a single file.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Excludes the header comment block that contains generation metadata like timestamp and user information. Use this when you need clean T-SQL output without documentation headers for automated
deployments.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Sets the character encoding for the output file. Defaults to UTF8 which handles international characters correctly. Change to ASCII only if you're certain the role names contain no special characters
and need compatibility with older systems.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | UTF8 |
| Accepted Values | ASCII,BigEndianUnicode,Byte,String,Unicode,UTF7,UTF8,Unknown |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |