Cross-database permissions are quite tricky. So, similar to my answer here:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I would suggest using module signing to accomplish this since, in the end, it will be more secure and yet less complex than the alternatives. This will require changing the View into a multi-statement Table-Valued Function so that it can be signed yet still be selected from.

Just do the following:

1. In the DB containing this view, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection).
2. Sign the TVF with the Certificate using ADD SIGNATURE
3. Back up the Certificate to files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key)
4. USE [master];
5. Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
6. Create a Login from that Certificate
7. Grant that Login whatever permissions are necessary to accomplish the goal (a.k.a. "git 'er done"). This might be a combination of CONNECT ANY DATABASE (I think that's in 2016) and something else, maybe even up to CONTROL SERVER. If no permission combination works, try instead to add this Login to the sysadmin fixed Server Role.

The permissions granted to the Certificate-based Login are a bit much, but that Login cannot be impersonated (i.e. EXECUTE AS LOGIN = N'that_Login';) and cannot log in; that Login is merely a container for the extra permissions and only ever apply to what has been signed with that Certificate, which in this case is just the one TVF, which is just a SELECT statement.

If you ever need to change the code within that TVF, or grant those permissions to another module via signing with ADD SIGNATURE, then you will need to either create that same Certificate in another DB if the new code to sign is in another DB, or you will need to restore the Private Key into this Certificate using the ALTER CERTIFICATE statement. While you can extract the public and private keys to VARBINARY hex byte strings using two built-in functions and then create a new Certificate from those VARBINARY strings (starting in SQL Server 2012), you can only restore a Certificate's private key from a .pvk file. Hence Step 3 above to use BACKUP CERTIFICATE as it can be used for both creating the full Certificate in any other DB as well as for restoring the private key into the existing Certificate here.

Cross-database permissions are quite tricky. So, similar to my answer here:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I would suggest using module signing to accomplish this since, in the end, it will be more secure and yet less complex than the alternatives. This will require changing the View into a multi-statement Table-Valued Function so that it can be signed yet still be selected from.

Just do the following:

1. In the DB containing this view, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection).
2. Sign the TVF with the Certificate using ADD SIGNATURE
3. Back up the Certificate to files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key)
4. Remove the Certificate's private key using ALTER CERTIFICATE
5. USE [master];
6. Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
7. Create a Login from that Certificate
8. Grant that Login whatever permissions are necessary to accomplish the goal (a.k.a. "git 'er done"). This might be a combination of CONNECT ANY DATABASE (I think that's in 2016) and something else, maybe even up to CONTROL SERVER. If no permission combination works, try instead to add this Login to the sysadmin fixed Server Role.

The permissions granted to the Certificate-based Login are a bit much, but that Login cannot be impersonated (i.e. EXECUTE AS LOGIN = N'that_Login';) and cannot log in; that Login is merely a container for the extra permissions and only ever apply to what has been signed with that Certificate, which in this case is just the one TVF, which is just a SELECT statement.

If you ever need to change the code within that TVF, or grant those permissions to another module via signing with ADD SIGNATURE, then you will need to either create that same Certificate in another DB if the new code to sign is in another DB, or you will need to restore the Private Key into this Certificate using the ALTER CERTIFICATE statement. While you can extract the public and private keys to VARBINARY hex byte strings using two built-in functions and then create a new Certificate from those VARBINARY strings (starting in SQL Server 2012), you can only restore a Certificate's private key from a .pvk file. Hence Step 3 above to use BACKUP CERTIFICATE as it can be used for both creating the full Certificate in any other DB as well as for restoring the private key into the existing Certificate here.

Cross-database permissions are quite tricky. So, similar to my answer here:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I would suggest using module signing to accomplish this since, in the end, it will be more secure and yet less complex than the alternatives. This will require changing the View into a multi-statement Table-Valued Function so that it can be signed yet still be selected from.

Just do the following:

1. In the DB containing this view, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection).
2. Sign the TVF with the Certificate
3. Back up the Certificate to files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key)
4. USE [master];
5. Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
6. Create a Login from that Certificate
7. Grant that Login whatever permissions are necessary to accomplish the goal (a.k.a. "git 'er done"). This might be a combination of CONNECT ANY DATABASE (I think that's in 2016) and something else, maybe even up to CONTROL SERVER. If no permission combination works, try instead to add this Login to the sysadmin fixed Server Role.

The permissions granted to the Certificate-based Login are a bit much, but that Login cannot be impersonated (i.e. EXECUTE AS LOGIN = N'that_Login';) and cannot log in; that Login is merely a container for the extra permissions and only ever apply to what has been signed with that Certificate, which in this case is just the one TVF, which is just a SELECT statement.

Cross-database permissions are quite tricky. So, similar to my answer here:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I would suggest using module signing to accomplish this since, in the end, it will be more secure and yet less complex than the alternatives. This will require changing the View into a multi-statement Table-Valued Function so that it can be signed yet still be selected from.

Just do the following:

1. In the DB containing this view, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection).
2. Sign the TVF with the Certificate using ADD SIGNATURE
3. Back up the Certificate to files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key)
4. USE [master];
5. Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
6. Create a Login from that Certificate
7. Grant that Login whatever permissions are necessary to accomplish the goal (a.k.a. "git 'er done"). This might be a combination of CONNECT ANY DATABASE (I think that's in 2016) and something else, maybe even up to CONTROL SERVER. If no permission combination works, try instead to add this Login to the sysadmin fixed Server Role.

The permissions granted to the Certificate-based Login are a bit much, but that Login cannot be impersonated (i.e. EXECUTE AS LOGIN = N'that_Login';) and cannot log in; that Login is merely a container for the extra permissions and only ever apply to what has been signed with that Certificate, which in this case is just the one TVF, which is just a SELECT statement.

If you ever need to change the code within that TVF, or grant those permissions to another module via signing with ADD SIGNATURE, then you will need to either create that same Certificate in another DB if the new code to sign is in another DB, or you will need to restore the Private Key into this Certificate using the ALTER CERTIFICATE statement. While you can extract the public and private keys to VARBINARY hex byte strings using two built-in functions and then create a new Certificate from those VARBINARY strings (starting in SQL Server 2012), you can only restore a Certificate's private key from a .pvk file. Hence Step 3 above to use BACKUP CERTIFICATE as it can be used for both creating the full Certificate in any other DB as well as for restoring the private key into the existing Certificate here.