Data-based solutions usually target a set of users who (ideally) have restricted permissions to the functionality. Therefore, as part of the process are defined several personas that target different use cases, for which the permissions must be restricted accordingly.
In the simplest scenario the user must have access to the underlying objects for querying the data. Supposing that an Entra User was created already, the respective user must be given access also in the Fabric database (see [1], [2]). From database's main menu follow the path to assign read permissions:
Security >> Manage SQL Security >> (select role: db_datareader)
 |
| Manage SQL Security |
Manage access >> Add >> (search for User)
 |
| Manage access |
 |
| Manage additional permissions |
The easiest way to test whether the permissions work before building the functionality is to login over SQL Server Management Studio (SSMS) and check the access using the Microsoft Entra MFA. Ideally, one should have a User's credentials that can be used only for testing purposes. After the above setup was done, the new User was able to access the data.
It's a good idea to try to understand what's happening in the background. For example, the expectation was that for the Entra User created above also a SQL user is created, which doesn't seem to be the case, at least per current functionality available.
Before diving deeper, it's useful to retrieve User's details:
-- retrieve current user SELECT SUser_Name() sys_user_name , User_Id() user_id , USER_NAME() user_name , current_user [current_user] , user [user];
| sys_user_name | user_id | user_name | current_user | user |
| JamesClavell@[domain].onmicrosoft.com | 0 | JamesClavell@[domain].onmicrosoft.com | JamesClavell@[domain].onmicrosoft.com | JamesClavell@[domain].onmicrosoft.com |
Retrieving the current User is useful especially when testing in parallel functionality with different Users. Strangely, User's ID is 0 when only read permissions were assigned. However, a valid User identifier is added for example when to the User is assigned also the db_datawriter role. Removing afterwards the db_datawriter role to the User keeps as expected User's ID. For troubleshooting purposes, at least per current functionality, it might be a good idea to create the Users with a valid User ID (e.g. by assigning temporarily the db_datawriter role to the User).
The next step is to look at the Users with access to the database:
-- database access SELECT USR.uid , USR.name --, USR.sid , USR.hasdbaccess , USR.islogin , USR.issqluser --, USR.createdate --, USR.updatedate FROM sys.sysusers USR WHERE USR.hasdbaccess = 1 AND USR.islogin = 1 ORDER BY uid
| uid | name | hasdbaccess | islogin | issqluser |
| 1 | dbo | 1 | 1 | 1 |
| 6 | CharlesDickens@[...].onmicrosoft.com | 1 | 1 | 0 |
| 7 | TestUser | 1 | 1 | 1 |
| 9 | JamesClavell@[...].onmicrosoft.com | 1 | 1 | 0 |
For testing purposes, besides the standard dbo role and two Entra-based roles, it was created also a SQL role to which was granted access to the SalesLT schema (see initial post):
-- create the user CREATE USER TestUser WITHOUT LOGIN; -- assign access to SalesLT schema GRANT SELECT ON SCHEMA::SalesLT TO TestUser; -- test impersonation (run together) EXECUTE AS USER = 'TestUser'; SELECT * FROM SalesLT.Customer; REVERT;
Notes:
1) Strangely, even if access was given explicitly only to the SalesLT schema, the TestUser User has access also to sys.sysusers and other DMVs. That's valid also for the access over SSMS
2) For the above created User there are no records in the sys.user_token and sys.login_token DMVs, in contrast with the user(s) created for administering the SQL database.
Let's look at the permissions granted explicitly:
-- permissions granted explicitly SELECT DPR.principal_id , DPR.name , DPR.type_desc , DPR.authentication_type_desc , DPE.state_desc , DPE.permission_name FROM sys.database_principals DPR JOIN sys.database_permissions DPE ON DPR.principal_id = DPE.grantee_principal_id WHERE DPR.principal_id != 0 -- removing the public user ORDER BY DPR.principal_id , DPE.permission_name;
| principal_id | name | type_desc | authentication_type_desc | state_desc | permission_name |
| 1 | dbo | SQL_USER | INSTANCE | GRANT | CONNECT |
| 6 | CharlesDickens@[...].onmicrosoft.com | EXTERNAL_USER | EXTERNAL | GRANT | AUTHENTICATE |
| 6 | CharlesDickens@[...].onmicrosoft.com | EXTERNAL_USER | EXTERNAL | GRANT | CONNECT |
| 7 | TestUser | SQL_USER | NONE | GRANT | CONNECT |
| 7 | TestUser | SQL_USER | NONE | GRANT | SELECT |
| 9 | JamesClavell@[...].onmicrosoft.com | EXTERNAL_USER | EXTERNAL | GRANT | CONNECT |
During troubleshooting it might be useful to check current user's permissions at the various levels via sys.fn_my_permissions:
-- retrieve database-scoped permissions for current user SELECT * FROM sys.fn_my_permissions(NULL, 'Database'); -- retrieve schema-scoped permissions for current user SELECT * FROM sys.fn_my_permissions('SalesLT', 'Schema'); -- retrieve object-scoped permissions for current user SELECT * FROM sys.fn_my_permissions('SalesLT.Customer', 'Object') WHERE permission_name = 'SELECT';
Notes:
1) See also [1] and [4] in what concerns the limitations that apply to managing permissions in SQL databases.
Happy coding!
Previous Post <<||>> Previous Post
References:
[1] Microsoft Learn (2024) Microsoft Fabric: Share your SQL database and manage permissions [link]
[2] Microsoft Learn (2024) Microsoft Fabric: Share data and manage access to your SQL database in Microsoft Fabric [link]
[3] Microsoft Learn (2024) Authorization in SQL database in Microsoft Fabric [link]
[4] Microsoft Learn (2024) Authentication in SQL database in Microsoft Fabric [link]
[5] Microsoft Fabric Learn (2025) Manage access for SQL databases in Microsoft Fabric with workspace roles and item permissions [link]
