May 03 2024 02:20 PM
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
May 05 2024 10:41 PM
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.
May 06 2024 06:15 AM
How is this related to linked servers?
We're talking about several databases on the same sql server database instance.