2

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?

4

sys.database_principals and sys.server_principals are protected views.

From the MSDN page on sys.database_principals:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.