0

I have a SQL Server A with DatabaseA, and I created 5 views based on tables from SQL Server B DatabaseB.

  • DatabaseA: login is userA, pwd: userA
  • DatabaseB: login is userB, pwd: userB

In the ASP.NET Core project startup, I have this code:

services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(Configuration.GetConnectionString("sqlConnection"), b => b.MigrationsAssembly("API")));

and this connection string in appsettings.json:

"ConnectionStrings": {
    "sqlConnection": "Server=TESTING\\SQLEXPRESS;Database=DatabaseA;User ID=userA;Password=userA;"
}

I get the error when I need to access the view

The server principal "userA" is not able to access the database "DatabaseB" under the current security context

How can I solve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user13717625
  • 23
  • 3
  • 1
    Why do you have multiple database-users in the first place? A Database User is not the same thing as a "user" in your application. There are legitimate reasons to have multiple database-users (principle-of-least-privilege, after all) but typically you have 1 user per application, so you won't need to use more than 1 userId in an EF connection-string for the reason. – Dai Jul 13 '21 at 08:10
  • It's probably a database permission issue. check out this link https://stackoverflow.com/questions/19009488 – MrMoeinM Jul 13 '21 at 08:11
  • When view the DbContext as the exception is thrown, what is the connection string that is set? Do both you databases have separate contexts or is one based on the other? – SpruceMoose Jul 13 '21 at 08:27
  • The way to make the trigger work is to create a user for LoginA in DatabaseB and grant permission to execute the stored procedure in DatabaseB.https://stackoverflow.com/questions/3837841/running-trigger-that-calls-stored-procedure-on-another-database – Tupac Jul 14 '21 at 06:16

0 Answers0