Share your SQL database and manage permissions
Applies to: ✅ SQL database in Microsoft Fabric
This article explains how to share a SQL database item and how to manage item permissions for a SQL database item.
For basic information about access control for SQL database in Fabric, see Authorization in SQL database.
Share a SQL database
Sharing is a convenient way to provide users and applications in your organizations with read access to your database. When sharing a database, you can decide how users and applications can consume your data:
- Using Transact-SQL queries against the database.
- Using Transact-SQL queries against the SQL analytics endpoint of the database.
- Accessing the mirrored data in OneLake, for example, using Spark.
- Creating Power BI reports using your database's semantic model.
Prerequisites
You must be an admin or a member in your workspace to share a SQL database item in Microsoft Fabric.
Share a database via Share quick action
- Locate a database, you want to share, in your workspace and select the Share quick action for it. Select the ellipsis
...button next to your database's name and select Share. - Search for and select a recipient you want to share the database with: a user, an application, or a group.
- Select additional permissions that determine if and how the recipient can consume your data.
- No additional permissions - Select no additional permissions if you plan to configure granular access for the recipient via SQL access controls. By default, the recipient is granted only the "Read" item permission. Read item permission allows the recipient to read properties of the SQL database, its SQL analytics endpoint, its default semantic model, and to connect to the SQL database and its SQL analytics endpoint. The recipient won't be able to query any table or view.
- Read all data using SQL database - Grants the recipient the ReadData item permission for the SQL database, allowing the recipient to read all data in the SQL database using Transact-SQL queries, for example by using SQL query editor for SQL database. You can grant the recipient access to more capabilities via SQL access controls.
- Read all data using SQL analytics endpoint - Grants the recipient the ReadData item permission for the SQL analytics endpoint, allowing the recipient to read all data via the SQL analytics endpoint using Transact-SQL queries, for example by using SQL query editor for SQL analytics endpoint. You can grant the recipient access to more capabilities by configuring SQL granular permissions for SQL analytics endpoint.
- Read all data using Apache Spark - Grants the ReadAll item permission to the recipient, allowing them to access the mirrored data in OneLake, for example, by using Spark or OneLake Explorer.
- Build reports on the default dataset - Grants the Build permission to the recipient, enabling users to Create simple reports on your SQL database in Power BI.
- Select Notify recipients by email to notify the recipients. The recipients receives an email with the link to the shared database.
- Select Grant.
Note
Granting item permissions has no impact on the security metadata inside the database. Specifically, it doesn't create any user objects (database-level principals). SQL database automatically creates a user object to represent the calling user/app in the database when a user or an app: is granted the Write item permission and doesn't have a user object in the database, creates an asymmetric key, a certificate, a schema, or a symmetric key. SQL database makes the new user object the owner of the created asymmetric key, certificate, schema, or symmetric key.
Manage permissions
To review item permissions granted to a SQL database, its SQL analytics endpoint, or its default semantic model, navigate to one of these items in the workspace and select the Manage permissions quick action.
If you have the Share permission for a SQL database, you can also use the Manage permissions page for the database to grant or revoke permissions.
Limitations
- It can take up to two hours for Fabric permission changes to be visible to users and applications.
- Granting item permissions for a database has no impact on the security metadata inside the database, like the metadata in the sys.database_permissions, sys.database_roles_members, and sys.database_principals catalog views. Therefore, to determine if a user or an application can access a database based on their item permissions, use the Manage permissions page in Fabric portal.