I have a successful connection from Azure data Factory to my Azure Sql db .And I have set the AAD Admin as myself and also the UserManagedIdentity from the portal.

Now whoever use that UserManagedidentity in ADF can access the entire Sql DB.I need to restrict the access at Schema level, like X people should have access to X tables and Y people should have access to Y Tables.

So how can we achieve this through usermangedIdentity ,Can we set Schema level permissions via usermanagedidentity?

CodePudding user response：

The managed identity has a corresponding user in SQL, so limit their permissions are you would any other user or group.

i.e.: GRANT SELECT ON Employees TO UserManagedIdentity;

CodePudding user response：

Admin overrides all other restrictions. So as long as a user is part of Server admin, he/she can have the entire access. For your use case, you would need to remove the managed identity from the admin group DL, create a new user within the database and grant the new user required access