dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Export-SqlUser

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.

You can choose destination version

When you run this command without specifying the -DestinationVersion parameter the script will be generated, by default, relying on current database compatibility level.
However, you can use this parameter to specify the destination version. Lets say you want to script a user from SQL Server 2014 and run the generated script on SQL Server 2008 version, you should use -DestinationVersion SQLServer2008/2008R2.
This way, any specific T-SQL code that only works on SQL 2012 will be scripted in a way that SQL Server 2008 can run without error.

Screenshots

Export_allUsers_db1_sql2008
Script all users from database db1 on sql2008 instance

 

Generated script for Export-SqlUser command for user named User1 on database bd1 that belongs to SQL2008 instance
Generated script for Export-SqlUser command for user named User1 on database bd1 that belongs to SQL2008 instance

Examples

Generate a script with all users on all databases within “sql2005” server and writes to the file “C:\temp\sql2005-users.sql”

Export-SqlUser -SqlServer sql2005 -FilePath C:\temp\sql2005-users.sql

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.

Export-SqlUser -SqlServer sqlserver2014a $scred -FilePath C:\temp\users.sql -Append

Exports ONLY users User1 and User2 from all databases on sqlsever2014a to the file C:\temp\users.sql

Export-SqlUser -SqlServer sqlserver2014a -User User1, User2 -FilePath C:\temp\users.sql

Exports user User1 from all databases on sqlsever2008 to the file C:\temp\users.sql with syntax to run on SQL Server 2016

Export-SqlUser -SqlServer sqlserver2008 -User User1 -FilePath C:\temp\users.sql -DestinationVersion SQLServer2016

Author

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Export-SqlUser -Detailed for more information on this function.

Source Code

Want to see the source code? View Export-SqlUser.ps1 on GitHub
 

Related commands