CODEWITHSUNDEEP
sqlsql-server-2008security
Peter Smith
Peter Smith

Reputation: 889

The server principal Xuser is not able to access the database Ydb under the current security context. sql server 2008

I'm trying to figure out why my stored procedure is not working for selecting from another database's column.

Databases Kdb and Ydb have the same database owner. Stored procedures and tables in both databases created using that database owner account.

There is a stored procedure in Ydb trying to access a table in Kdb being executed by a minimal rights user (only has execute and connect on Ydb, no rights to Kdb).

Use [Ydb]
GO
ALTER PROCEDURE  [dbo].[CreateSecureTableAEntry]
@fid int
AS
BEGIN
DECLARE @operatorId uniqueidentifier
DECLARE @orgName nvarchar(max)
SELECT @orgName = SUBSTRING(SYSTEM_USER,1,LEN(SYSTEM_USER)-4)
SELECT @operatorId = Id FROM Kdb.dbo.Organizations WHERE Kdb.dbo.Organizations.Name =  @orgName
INSERT INTO TableA (operatorId, sourceFid) VALUES (@operatorId, @fid);
END

I have enabled cross database chaining on both Kdb and Ydb. I have made Ydb trustworthy.

This is clearly a permissions problem, but what else should I do while keeping permissions as tight as possible?

Upvotes: 1

Views: 1697

Answers (1)

Peter Smith
Peter Smith

Reputation: 889

I resolved this problem successfully by incorporating the select query into a view inside Ydb and enabling the guest account on Kdb with the following query:

USE [Kdb];
GO
GRANT CONNECT TO guest;

Upvotes: 1

Related Questions