Direct Permission

Every Microsoft Azure SQL Database securable has associated permissions that can be granted to a principal. Permissions are managed at the server level assigned to logins and server roles, and at the database level assigned to database users and database roles. The model for Azure SQL Database has the database permissions, but the server level permissions aren't available.

The Azure SQL Database connector supports the database level permissions assigned to database users that are mapped to a server login or to the database roles as direct permissions.

The connector supports direct permissions assigned onto following targets (securables):

  • DATABASE

  • DATABASE_PRINCIPAL (for example, Database User or Database Role)

  • SCHEMA

  • ASSEMBLY

  • ASYMMETRIC_KEY

  • SYMMETRIC_KEYS

  • OBJECT_OR_COLUMN (for example, Stored procedure, Table, or Table column)

For example:

  1. GRANT CONNECT on 'databaseuser@databasename:DATABASE'

  2. GRANT EXECUTE on 'databaseuser@databasename:[dbo].[getCustomer](SQL_STORED_PROCEDURE)'

  3. GRANT_WITH_GRANT_OPTION CREATE VIEW on 'databaseuser@databasename:DATABASE'

Note
In accordance with the behavior of Microsoft Azure SQL Database, revocation of permissions on special database roles like loginmanager, dbmanager, and fixed database roles is not applicable.

Enable Direct Permissions for Aggregation

To enable direct permissions and their aggregation for the account and group schema, complete the following:

  1. Use the listSources API to get the source ID.

  2. use the listSchemas API to get the schema ID for the account or group schema for which you want to enable direct permissions.

  3. Use the updateSchema API and enter the following content in body, with the sourceID and the schemaID in the header:

    Copy 
    [
        {"op": "add", "path": "/includePermissions", "value": true}
    ]