Atlas now manages SQL Server synonyms as synonym blocks, together with the permissions granted on them.

atlas schema inspect prints synonyms as synonym blocks, and atlas migrate diff and atlas schema apply plan their changes and the permissions granted on them.

Declaring Synonyms

The object attribute names the base object of a synonym:

A reference, such as table.t1, when Atlas manages the base table, view, function, or procedure.
A multi-part name otherwise, such as an object in another database, on a linked server, or one that doesn't exist yet.

Starting from this SQL schema:

schema.sql

CREATE TABLE t1 (id int NOT NULL);
CREATE TABLE t2 (id int NOT NULL);
CREATE SYNONYM s_t1 FOR t1;
CREATE SYNONYM s_db FOR otherdb..t1;
CREATE SYNONYM s_remote FOR srv.otherdb.dbo.t1;
CREATE SYNONYM s_missing FOR missing;
CREATE VIEW v1 AS SELECT id FROM s_t1;
CREATE SYNONYM s_v1 FOR [dbo].[v1];

atlas schema inspect prints the synonyms, and the view that selects from s_t1 depends on it:

schema.ms.hcl

view "v1" {
  schema = schema.dbo
  column "id" {
    null = false
    type = int
  }
  as         = "SELECT id FROM s_t1"
  depends_on = [synonym.s_t1]
}
synonym "s_db" {
  schema = schema.dbo
  object = "[otherdb]..[t1]"
}
synonym "s_missing" {
  schema = schema.dbo
  object = "[missing]"
}
synonym "s_remote" {
  schema = schema.dbo
  object = "[srv].[otherdb].[dbo].[t1]"
}
synonym "s_t1" {
  schema = schema.dbo
  object = table.t1
}
synonym "s_v1" {
  schema = schema.dbo
  object = view.v1
}

atlas migrate diff creates a synonym after its base object, and a view that selects from a synonym after the synonym:

-- Create "t1" table
CREATE TABLE [t1] (
  [id] int NOT NULL
);
-- Create synonym "s_t1"
CREATE SYNONYM [s_t1] FOR [t1];
-- Create "v1" view
CREATE VIEW [v1] ([id])
AS
SELECT id FROM s_t1;
-- Create synonym "s_v1"
CREATE SYNONYM [s_v1] FOR [v1];

Changing the Base Object

SQL Server has no ALTER SYNONYM. When the base object changes, here s_t1 moving from t1 to t2, Atlas drops and re-creates the synonym:

schema.ms.hcl

synonym "s_t1" {
  schema = schema.dbo
  object = table.t2
}

-- Drop synonym "s_t1"
DROP SYNONYM [s_t1];
-- Create synonym "s_t1"
CREATE SYNONYM [s_t1] FOR [t2];

Permissions

With roles and permissions enabled, permission blocks grant privileges on synonyms as they do on tables:

schema.ms.hcl

synonym "s_orders" {
  schema = schema.dbo
  object = table.orders
}
synonym "s_current" {
  schema = schema.dbo
  object = table.orders
}
role "reader" {}
role "writer" {}
permission {
  to         = role.reader
  for        = synonym.s_orders
  privileges = [SELECT]
}
permission {
  to         = role.writer
  for        = synonym.s_orders
  privileges = [INSERT, UPDATE]
  grantable  = true
}
permission {
  to         = role.reader
  for        = synonym.s_current
  privileges = [SELECT]
}

In the next version, s_current points to the archive table, writer keeps only INSERT on s_orders, and reader also gets DELETE. Atlas re-creates s_current and grants its permissions again, and changes the grants of s_orders in place:

-- Drop synonym "s_current"
DROP SYNONYM [dbo].[s_current];
-- Create synonym "s_current"
CREATE SYNONYM [dbo].[s_current] FOR [dbo].[archive];
-- Grant on synonym "s_current" to "reader"
GRANT SELECT ON [dbo].[s_current] TO [reader];
-- Revoke on synonym "s_orders" from "writer"
REVOKE UPDATE ON [dbo].[s_orders] FROM [writer] CASCADE;
-- Grant on synonym "s_orders" to "reader"
GRANT DELETE ON [dbo].[s_orders] TO [reader];

atlas migrate down restores a dropped synonym with its grants.

Notes

A base object in another schema is a reference when the URL covers the whole database, and a multi-part name when the URL is scoped to one schema.
A synonym can replace a table of the same name, and a table can replace a synonym. Atlas drops one before it creates the other.
Comments on synonyms are not managed.
Requires Atlas Pro. Run atlas login to start a free trial.

SQL Server: Synonyms

Declaring Synonyms

Changing the Base Object

Permissions

Notes