Permissions issue, maybe?

  • 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.

     

     

     

  • This was removed by the editor as SPAM

  • 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