how to grant permissions to all the synonyms inside a view? I have to grant permissions to a view.  dbo.my_view, however, this view joins many tables, and other views and synonyms.

using the following script I can find all all the synonyms in my server:

Script to list synonym contents

SELECT
name as synonymName,
base_object_name as synonymDefinition,
COALESCE(PARSENAME(base_object_name,4),@@SERVERNAME) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
FROM sys.synonyms
ORDER BY serverName,dbName,schemaName,objectName


If I have to grant select on dbo.myview how can I find out what select permissions I need to grant in any other databases that because of the synonyms also need to be granted?
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:


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).
Sign the TVF with the Certificate using ADD SIGNATURE
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)
Remove the Certificate's private key using ALTER CERTIFICATE
USE [master];
Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
Create a Login from that Certificate
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.