I (Sheraz) am a dbo on a SQL server “MyServer”, I have created a SQL Server User “Reader” who can read specific table(s) and can run a few stored procedures.
These stored procedures have been granted execute permission for user “reader” and also these stored procedures has been created using “With Execute as Owner”.
I am using Enterprize Library (4.1)’s data Access Layer on Asp.NET(3.5) to connect to “MyServer”. I have setup a connection and passed the Username/Password
for the user “Reader”.
Now If I try to read the data using Select command that is being fetched right but if I try to run the stored procedure using ExecuteDataset it gives me the following error.
The server principal “Domain\Sheraz” is not able to access the database “MainDB” under the current security context.
Now, I am surprised that why it is trying to run the storedprocedure as Sheraz, because I have passed the connection with user “Reader”. Also the stored procedure
is in “MyDB2” ( though it inserts the data in a table in “MainDB”) with settings to run with owner’s permissions.
Strangely enough, It shows Sheraz not having access to perform this action when he is dbo with permissions. Also, it is displaying name of a database which should be the error.
I know I am victim of bizarre permission-Chain(or something to do with trust) but can not figure out a way to resolve the issue. I do want to use Enterprise library and also would
like to run these under user “Reader”.
Anyone please explain it a bit clearly how I can do it. (Apologies if it is in wrong place)
Regards, Sheraz MCP(2), MCTS(2), MCITP(2)