Dynamic sql within stored procedure security context

[EdSpa290]

Hello,

We have stored procedure in database dbA, that runs (among other things) dynamic sql. It accesses several dbs, not just the one where stored procedure is (dbA). Db user domainname\username has EXECUTE permission on this stored procedure.

When user runs this stored procedure, it is failing with the error that says "the server principle "domainname\username" is not able to access the database "dbB" under the current security context"

Note that domainname\username clearly exists in database dbB, it is not disabled, and following sql proves it:

select * from dbB.sys.sysusers where name = 'domainname\username'

I know that dynamic sql within stored procedure changes security context of its execution, but I would expect that since this user exists in all dbs (including dbB) it should not be the problem.

Does anybody have any idea what could be the issue?

Thanks

[#2]

@EdSpa290 

---

Does anybody have any idea what could be the issue?

---

Beeing a SQL Server principal/database user isn't enough, the security setting on the linked server must be the right one, see

Create linked servers - SQL Server | Microsoft Learn => Permissions.

[#3]

@olafhelper ,

How is this related to linked servers?

We're talking about several databases on the same sql server database instance.