How to access any sys.database_principals view without sysadmin server role?
I have a requirement to monitor various aspects (users, roles etc) of all our SQL databases spread over a number of servers from one central database. The connections to other servers will be via a common Windows authenticated login and the requirement specifies this login can not have the sysadmin server role. I thought I would be able to achieve this via VIEW ANY DATABASE or VIEW ANY DEFINITION but apparently not. I do not want to have to set up specific permissions from within each database or use SSIS either. Having search around the only way I can think of doing this is to grant the login CONTROL SERVER and then revoke all of the roles/privileges it does not need. This seems a very long winded way of achieving the objective. Does anyone know a way of doing this?