The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I can't seem to find what's the essence regarding the difference in remote and consolidated user. When to use what??

From the documentation:

REMOTE All recipients of messages from the current database, or senders of messages to the current database, who are immediately lower on the SQL Remote hierarchy than the current database must be granted REMOTE permissions.

CONSOLIDATE At most one user ID may be granted CONSOLIDATE permissions in a database. CONSOLIDATE permissions identifies a database immediately above the current database in a SQL Remote setup. Each database can have only one consolidated database directly above it.

It's hard to really understand the difference based on this description.

Take this scenario where I have a main database (A) and two satelite databases (B,C) which have their own subscription to the same publication. I would use two remote users in the main database and let dbremote do the replication. In what scenario do I need a consolidated user?

alt text

asked 05 Dec '12, 04:01

OBR's gravatar image

accept rate: 0%

edited 05 Dec '12, 15:43

Publishers of B and C need to GRANT CONSOLIDATE to publisher of A.

Publisher A needs to grant REMOTE to publishers of B and C.

In a single-tiered hierarchy, there is one consolidated database with one or more remote databases underneath. In such a hierarchy, the consolidated database grants REMOTE permission to the publishers of the remote databases. Each remote database grants CONSOLIDATE permission to the consolidated database publisher.

For example, there is a consolidated database identified by its publisher, HeadOffice, and a remote database identified by its publisher, RegionalOffice.

On the consolidated database, HeadOffice, you:

  • Create a user with the same name as the publisher of the remote database: RegionalOffice.

  • Grant REMOTE permission to RegionalOffice. This identifies RegionalOffice as a database that receives messages from HeadOffice.

On the remote database, RegionalOffice, you:

  • Create a user with the same name as the publisher of the consolidated database: HeadOffice.

  • Grant CONSOLIDATE permission to HeadOffice. This identifies HeadOffice as the consolidated database for RegionalOffice; that is, HeadOffice is the database that sends messages to RegionalOffice.

permanent link

answered 05 Dec '12, 16:02

Andy%20Rogers's gravatar image

Andy Rogers
accept rate: 33%

edited 05 Dec '12, 16:08


If you grant CONSOLIDATE to a user, that means you are sending/receiveing messages to/from a database higher in the hiearchy. If you grant REMOTE to a user, that means this database is lower in the hierarchy.

Why the difference? The type of user you define (REMOTE vs. CONSOLIDATE) will have an effect on conflict resolution, and whether or not SQL Remote will perform conflict resolution when it is aplying an UPDATE that will cause a conflict. Conflict resolution triggers will only fire if they are received from a remote user that have been granted REMOTE. If the user has been granted CONSOLIDATE, no conflict resolution triggers will fire. Using your picture as an example, this means that only Database A will perform conflict resolution. If all the databases in the system were attempting to do conflict resolution, you could easily set up an infinite conflict resolution loop.

(06 Dec '12, 08:46) Reg Domaratzki

Conceptually, a SQL Remote system is a hierarchy of databases.

In a single-tiered hierarchy there is a single, consolidated database at the top and underneath are one or more remote databases.

In a multi-tiered hierarchy, again there are remote databases at the bottom. However, in the tier immediately above the remote databases are databases that are both consolidated for the remote databases in the tier below, and also remote for the database immediately above them in the hierarchy, and so on, until you reach the top. At the top is, again, a single consolidated database.

Both the consolidated database and the remote databases need to identify which database(s) they can send messages to and receive messages from. Each database also has a userid associated with it. The user associated with that userid is called the publisher of the database.

For remote databases,you must GRANT CONSOLIDATE to the publisher (identified by the publisher’s userid) of the consolidated database they will be communicating with (the database above it in the hierarchy). The remote database can only communicate with one consolidated database (a one-to-one relationship).

For consolidated databases, you must GRANT REMOTE to the userid of the publisher of each remote database (in the tier below the consolidated) it will be communicating with (a potentially one-to-many relationship).

In a multi-tiered hierarchy there will be users who are granted both REMOTE and CONSOLIDATE (publishers of the databases in the middle tiers).

Here are some links to the SQL Remote documentation to assist you further:

Single-tiered hierarchy:

Multi-tiered hierarchy:

Permissions overview (PUBLISH, REMOTE, CONSOLIDATE):

CONSOLIDATE permission:


REMOTE permission:

GRANT REMOTE statement:

If you have any further questions, please feel free to follow up.

Best Regards, Andy

permanent link

answered 05 Dec '12, 15:58

Andy%20Rogers's gravatar image

Andy Rogers
accept rate: 33%

The Consolidated user is the user higher up in the hierarchy the remotes are the users lower in the hierarchy. This is why you can have multiple remote users but only one consolidated user.


permanent link

answered 05 Dec '12, 12:42

J%20Diaz's gravatar image

J Diaz
accept rate: 14%

But why would I need a consolidated user? Let's say I have a main database and two satelite database each of it's own subscription to the same publication. For this I would add two remote users. Where does the consolidated user fit in? Can't see the scenario for when to use consolidated

(05 Dec '12, 15:35) OBR

The consolidate user is the user on database A in your design. This user has one (or multiple) addresses how remote users B+C can reach it. For example User B could communicate with consolidate A via FTP, and user C can communicate via email with the consolidate.

And changes made by B get replicated up to A, and then from there down to C

(05 Dec '12, 16:20) ASchild
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 05 Dec '12, 04:01

question was seen: 1,060 times

last updated: 06 Dec '12, 08:49