SQL Server - How to transfer logins between instances by backing up users, logins, and permissions

Views: 2.640
Reading Time: 11 minutes

Hello people,
All right with you ?

In this post I will demonstrate a very cool script that I developed that aims to generate SQL scripts to back up (reverse engineer) permissions, users, logins and roles of an instance in SQL Server.

This script is designed to be used for instance migrations, either for a new server or for a version upgrade of your SQL Server instance, in a practical and easy to use way. If you also want to migrate SQL Agent jobs to another instance as well, see more by accessing the post. SQL Server - How to back up all SQL Agent jobs via command line (CLR C # or Powershell).

Using the Stored Procedure below, you can generate SQL commands to perform the following tasks:

  • Script for logging backup (Windows AD and SQL Server Authentication)
  • Script to backup instance login permissions (Server level permissions)
  • Script for backing up roles in instance roles (Server roles permissions)
  • Script for backing up user creation in databases (COM and SEM login)
  • Script to backup database login permissions (Database level permissions)
  • Script for backing up database roles permissions

As prerequisites for using stpExporta_Logins, you will need:

The script of stpExports_Logins, which I will present in this post, was inspired by the post's Stored Procedure SQL Server - How to copy / replicate a user's permissions, where the procedure in this post aims to generate SQL files to replicate permissions to another environment, while the script in another post aims to copy permissions from one user to another in the same instance and therefore has no validations and checks, such as logging in.

StpExporta_Logins Procedure Source Code

To perform all tasks easily, simply run the source code below and create stpExporta_Logins in a database of your choice.

This Stored Procedure uses SP stpWrite_File_File to physically write the files to disk, which requires the resource to Ole Automation Procedures is enabled on the instance.

However, you do not have to worry about this part as SP has a check that if this feature is not enabled on the instance, SP itself will enable it during execution and will disable it after the generation of files, keeping this setting just as it was before the procedure was executed.

Source code:

Examples of use

1 Example: Exporting 1 Script by User

Generated Files:

Windows AD Authentication User:

User with SQL authentication and password expiration and security policies:

User with SQL authentication and no login:

User with SQL authentication and different user login:

2 Example: Exporting 1 Script Per Instance

Generated Files:

Preview of the generated script:

That's it, guys.
I hope this post was helpful to you.

Hug!

SQL Server - How to Transfer Logins Between Instances Generating User Backups, Logins, and Permissions Reverse Engineering Backup Logins Users Permissions Roles Server Database Migrate Logins Transfer Logins Users

SQL Server - How to Transfer Logins Between Instances Generating User Backups, Logins, and Permissions Reverse Engineering Backup Logins Users Permissions Roles Server Database Migrate Logins Transfer Logins Users