logoReverse Migrations

Microsoft SQL Server Service Account

Reverse Migrations only needs to read schema metadata — tables, columns, data types, constraints, and indexes. It never reads the actual rows in your tables.

The steps below create a dedicated login that can inspect the schema but cannot SELECT from user tables.

1. Create the server login

Connect to the master database as an administrator (for example a member of the sysadmin role) and run:

CREATE LOGIN reverse_migrations WITH PASSWORD = 'a-strong-password';

2. Create the database user and grant metadata permissions

For each database you want to introspect, run:

USE [your_database];
CREATE USER reverse_migrations FOR LOGIN reverse_migrations;

-- Allow connection to the database
GRANT CONNECT TO reverse_migrations;

-- Allow reading object definitions (schema metadata) without table data access
GRANT VIEW DEFINITION TO reverse_migrations;

Why VIEW DEFINITION?
This permission lets the user query catalog views such as sys.tables, sys.columns, sys.indexes, and INFORMATION_SCHEMA tables, but it does not grant the ability to run SELECT against user tables.

If you also want the account to be able to list all databases on the instance, run this once in master:

USE [master];
GRANT VIEW ANY DATABASE TO reverse_migrations;

3. Verify the user cannot read table data

Connect as reverse_migrations and try to query a user table. It should fail:

SELECT * FROM your_database.dbo.some_user_table;
-- Msg 229, Level 14, State 5: The SELECT permission was denied on the object 'some_user_table'

Schema queries should succeed:

SELECT * FROM your_database.sys.tables;
SELECT * FROM your_database.INFORMATION_SCHEMA.TABLES;

4. Use this credential in the dashboard

Copy the username (reverse_migrations) and password into the Reverse Migrations dashboard when you add your MsSQL database.

References

Back to Documentation