locked
"Module being executed is not trusted" exception when accessing AG information RRS feed

  • Question

  • Debugging an issue with Service Broker. The issue can be replicated using this script:

    CREATE USER [test_user]
    WITHOUT LOGIN;
    GO
    EXECUTE AS USER = N'test_user';
    GO
    SELECT 1 FROM [master].[sys].[availability_groups];
    GO
    SELECT 1 FROM [master].[sys].[availability_databases_cluster];
    GO
    REVERT;
    GO
    

    This returns the following:

    (0 rows affected)
    Msg 15562, Level 16, State 1, Line 8
    The module being executed is not trusted. Either the owner of the database of the
    module needs to be granted authenticate permission, or the module needs to be 
    digitally signed.

    Notice that the user CAN select from [sys].[availability_groups] but NOT [sys].[availability_databases_cluster]. (A user can access the sys tables but only some of the AG-related tables.)

    This started as a requirement to get the AG name associated with a database, which I turned into a UDF:

    CREATE FUNCTION [dbo].[isPrimaryNode]
    () -- Parameterless function
    RETURNS bit
    AS
    BEGIN
      DECLARE @return_value bit = 0;
    
      -- STEP 2: Check Availability Group
      SELECT
        @return_value = 1
      WHERE
        NOT EXISTS(SELECT 1 FROM [master].[sys].[availability_groups])
    
      -- STEP 2: Check Availability Group Node State
      IF (@return_value = 0)
        SELECT
          @return_value = 1
        FROM
          [master].[sys].[availability_groups]
          INNER JOIN [master].[sys].[availability_replicas]
            ON ([availability_groups].[group_id] = [availability_replicas].[group_id])
          INNER JOIN [master].[sys].[dm_hadr_availability_replica_states]
            ON ([availability_replicas].[group_id] = [dm_hadr_availability_replica_states].[group_id])
            AND ([availability_replicas].[replica_id] = [dm_hadr_availability_replica_states].[replica_id])
        WHERE
          ([dm_hadr_availability_replica_states].[is_local] = 1)
          AND ([dm_hadr_availability_replica_states].[role_desc] = N'PRIMARY')
          AND ([availability_replicas].[replica_server_name] = @@SERVERNAME);
    
      RETURN @return_value;
    END
    GO
    

    I can default to @@SERVERNAME for the interim until this gets fixed, but I cannot for the life of me figure out what permissions to grant on the local database -- I've tried EXECUTE, CONTROL, and REFERENCES on both [sys] and [INFORMATION_SCHEMA] to no avail.


    Tuesday, February 20, 2018 8:42 PM

Answers

  • Hi texasrebelaggie,

    >>Notice that the user CAN select from [sys].[availability_groups] but NOT [sys].[availability_databases_cluster]. 

    The permission on [sys].[availability_groups] and [sys].[availability_databases_cluster] are different, for [sys].[availability_groups], it requires VIEW ANY DEFINITION permission on the server instance, for [sys].[availability_databases_cluster], it requires the minimum permissions ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission to see the corresponding row, or CREATE DATABASE permission in the master database.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Teige Gao Monday, February 26, 2018 3:00 AM
    • Marked as answer by texasrebelaggie Monday, February 26, 2018 3:32 PM
    Thursday, February 22, 2018 9:40 AM

All replies

  • Hi texasrebelaggie,

    >>Notice that the user CAN select from [sys].[availability_groups] but NOT [sys].[availability_databases_cluster]. 

    The permission on [sys].[availability_groups] and [sys].[availability_databases_cluster] are different, for [sys].[availability_groups], it requires VIEW ANY DEFINITION permission on the server instance, for [sys].[availability_databases_cluster], it requires the minimum permissions ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission to see the corresponding row, or CREATE DATABASE permission in the master database.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Teige Gao Monday, February 26, 2018 3:00 AM
    • Marked as answer by texasrebelaggie Monday, February 26, 2018 3:32 PM
    Thursday, February 22, 2018 9:40 AM
  • I'll throw the suggestion up to the SQL Server team that we should have the equivalent of @@SERVERNAME for a database AG name applicable to the public role. (Which is sort of what I was trying to accomplish by turning it into a function, but I absolutely refuse to grant elevated permissions to the public role.)

    Monday, February 26, 2018 3:36 PM