I'm attempting to get a listing of the users and roles they are associated with on a remote server to be input into a database on the local server. But when I use the linked server sys.database_principals only shows me information for the login that the linked server is using. How do I get a listing of users and roles by database to a central db location?
sys.server_principals are protected views.
From the MSDN page on
Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.
You need to ensure the login used by the linked server has at least the
ALTER ANY USER or
ALTER ANY ROLE privilege in order to see the other principals.