how to grant permissions to all the synonyms inside a view?

Question

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?

Answer 1

I think Marcello was asked about some query like this:

select  d.referenced_entity_name,
        s.name as synonymName,
        base_object_name as synonymDefinition,
        COALESCE(PARSENAME(s.base_object_name,4),@@SERVERNAME) AS serverName,
        COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID())) AS dbName,
        COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
        PARSENAME(s.base_object_name,1) AS objectName
from sys.sql_expression_dependencies d
     join sys.synonyms s
        on d.referenced_id = s.object_id
where d.referencing_id = object_id('dbo.myView'); 

This query simply shows the synonyms used in the view dbo.myView and their definitions using Marcello's query.

While certificates is a valid approach it may be impossible to turn all views to UDF, besides, I'm not sure if cerfificate approach can be implemented between different servers, I mean some synonyms can reference linked servers.

And this query shows all views that use synonyms along with synonym definitions:

select  distinct
        o.name as view_name,
        --d.referenced_entity_name,
        s.name as synonymName,
        base_object_name as synonymDefinition,
        COALESCE(PARSENAME(s.base_object_name,4),@@SERVERNAME) AS serverName,
        COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID())) AS dbName,
        COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
        PARSENAME(s.base_object_name,1) AS objectName
from sys.sql_expression_dependencies d
     join sys.objects o
        on o.object_id = d.referencing_id and o.type = 'V'
     join sys.synonyms s
        on d.referenced_id = s.object_id
order by 1;

Answer 2

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.