Filter Replication set by username

Question

I have a Merge Replication setup between a central server and 200 clients. The vast majority of the clients do not need all 5000 client records. Only the 50 or so that are assigned to them. How would I go about applying a Filter based on who's logged in?

Should I create a NEW DB that maps user names to Client Records and go down that path?

Any better ideas or pitfalls? How is everyone handle this kind of per user filtering?

Thanks!

Answer 1

I know that MS Dynamics CRM use view on the server with a huge select to look at who has permission to see what. When someone query that view it only returns what he can see.

Maybe you could do something like that.

Answer 2

To do this use either HOST_NAME() or SUSER_SNAME() to filter by either the host name or current user respectively.

Use the below when building the filter statement in the Publication Properties.

i.e. salesrep = SUSER_SNAME()

See SQL Docs for more info.