January 18, 2007 at 10:28 am
Help please. SQL Server 7. VB6 app.
We have a VB app that calls a sp in db_A. This sp is supposed to update a table in db_A, then update a table in db_B, then update a table in db_C. All of this wrapped in a transaction as all updates must succeed, or rollback.
I am getting a SELECT permissions denied on the update statement affecting the table in db_B. If this update statement is commented out, the updates in db_A and db_C complete and the program succeeds.
I've also tried re-writing the sp to update the db_A table, then call an update stored procedure in db_B, then call an update stored procedure in db_C. Something like this..
CREATE PROCEDURE procA
UPDATE TABLE A
WHERE something
EXEC DB_B.dbo.procB @something, @something
EXEC DB_C.dbo.ProcC @something, @something
EXEC DB_C.dbo.ProcC2 @something, @something
If EXEC DB_B.dbo.procB @something, @something is commented out, the procedure executes and program completes. But with EXEC DB_B.dbo.procB @something, @something in play, I am getting a SELECT Permission denied again.
The login we are testing with exists in all databases. Logins inherit permissions through Public. The only way we've been able to get this to work is by applying explicit permissions to the problem objects.
I've just been unable to identify any differences in the databases, or users, permissions, etc.
Any thoughts would be greatly appreciated.
January 22, 2007 at 8:00 am
This was removed by the editor as SPAM
January 22, 2007 at 1:03 pm
I have since checked for orphaned users (sp_change_users_login) - no orphans.
The owner of Database_A which contains the table referenced in the permission denied message is SA.
The database which contains the sp which is attempting to update the Database_A table was not owner SA. I've since changed owner to SA thinking that was the problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply