What table should I host data linking two objects from separate tables?
If Users can only be members of one group, you need only store a GroupId in the Users table. If not, the pattern you are looking for is commonly known as a "Join" table, containing only foreign keys and perhaps a generated primary key.

If you have primary key UserId (uniquely identifies a user record) for the Users table and GroupId for the Groups table, you can create a third table:

CREATE TABLE [dbo].[GroupUsers](
    [UserId] [int] NOT NULL,
    [GroupId] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Groups] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([GroupId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Groups]
GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Users]
GO


This design allows for one-to-one, one-to-many and many-to-many relationships. In practice, its performance and scalability are excellent, as long as the tables are properly indexed.

To get all users in a group given its GroupId:

SELECT *
FROM Users u
INNER JOIN GroupUsers gu
ON u.UserId = gu.UserId
WHERE gu.GroupId = @GroupId


Many times the relationships need to be traversed across the foreign key table. For instance, to select the groups a user belongs to, given their username (assuming there is a Username field in Users):

SELECT g.* 
FROM Groups g
INNER JOIN GroupUsers gu
ON g.GroupId = gu.GroupId
INNER JOIN Users u
ON gu.UserId = u.UserId
WHERE u.Username = @Username


I usually use views to simplify queries that need this sort of traversal.