Export SQL Server logins

This document describes how to export SQL Server logins, security identifiers (SIDs), and password hashes from a Cloud SQL for SQL Server instance by using the sp_help_revlogin stored procedure.

When you migrate databases or set up database synchronization between SQL Server instances, you must re-create user logins on the destination instance with matching SIDs and password hashes. This ensures that database users remain mapped to their corresponding server logins and retain their permissions.

Cloud SQL for SQL Server provides the sp_help_revlogin stored procedure in the msdb database to generate Transact-SQL (T-SQL) scripts for recreating user logins.

Before you begin

Required roles

To get the permission that you need to configure database flags, ask your administrator to grant you the following IAM roles on the project:

• Cloud SQL Admin (roles/cloudsql.admin)
• Cloud SQL Editor (roles/cloudsql.editor)

For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the cloudsql.instances.update permission, which is required to configure database flags.

You might also be able to get this permission with custom roles or other predefined roles.

Database permissions

Ensure that you have access to the default sqlserver SQL Server user role.

Enable the database flag

To install the sp_help_revlogin stored procedure in the msdb database, enable the cloud sql enable sp_help_revlogin database flag on your instance.

gcloud sql instances patch INSTANCE_NAME \
    --database-flags="cloud sql enable sp_help_revlogin=on"

Connect using a supported client tool

The sp_help_revlogin stored procedure outputs generated CREATE LOGIN scripts using T-SQL PRINT statements (informational messages) rather than tabular result sets (SELECT statements).

Connect to your Cloud SQL instance by using one of the following tools:

• SQL Server Management Studio (SSMS): connect to the instance, execute the procedure, and view the generated scripts in the Messages tab. Alternatively, press Control+T to switch the execution output mode to Results to Text before executing the query.
• Visual Studio Code: connect by using the MSSQL extension, execute the procedure, and view the generated scripts in the Messages tab.

• sqlcmd utility: connect to the instance and output the generated scripts directly to a SQL file:

  sqlcmd -S INSTANCE_IP \
      -U USERNAME \
      -P PASSWORD -d msdb \
      -Q "EXEC dbo.sp_help_revlogin" -o output_logins.sql
  

  Replace the following:

  • INSTANCE_IP: the IP address of your Cloud SQL instance.
  • USERNAME: your administrative database username (such as sqlserver).
  • PASSWORD: your database user password.

Export logins by using sp_help_revlogin

Connect to the msdb database and run the sp_help_revlogin stored procedure.

• To export all customer logins:

  EXEC msdb.dbo.sp_help_revlogin;
  

• To export a specific login:

  EXEC msdb.dbo.sp_help_revlogin
      @login_name = 'LOGIN_NAME';
  

  Replace LOGIN_NAME with the name of the login to export.

Re-create logins on the destination instance

1. Copy the generated CREATE LOGIN statements from the query output.
2. Connect to your destination SQL Server instance.
3. Execute the generated statements in a query window or by using sqlcmd.

The generated statements create the logins on the destination instance with their original SIDs, default databases, and password hashes. For more information about considerations when transferring logins across instances, see the Microsoft documentation on Transferring logins and passwords between instances of SQL Server.

Limitations and excluded logins

sp_help_revlogin automatically excludes the following types of logins from the export:

• Google Cloud service accounts and internal management accounts.
• Internal SQL Server system accounts (logins prefixed with ##).
• Logins assigned to the sysadmin fixed server role.
• Logins assigned to restricted administrative server roles.

Disable the database flag

If you no longer need the stored procedure, then set the flag to off (or remove the flag from the instance):

gcloud sql instances patch INSTANCE_NAME \
    --database-flags="cloud sql enable sp_help_revlogin=off"

When set to off or removed, Cloud SQL automatically drops dbo.sp_help_revlogin from the msdb database.

What's next

• For more information about SQL Server users, see SQL Server users.
• For more information about configuring database flags, see Configure database flags.
• For more information about supported stored procedures, see SQL Server stored procedures.
• For more information about transferring logins and passwords, see Transferring logins and passwords between instances of SQL Server.