Stored Procedure Permissions Problem

Posted by JimR on Stack Overflow See other posts from Stack Overflow or by JimR
Published on 2010-05-26T11:40:56Z Indexed on 2010/05/26 13:21 UTC
Read the original article Hit count: 201

Filed under:
|

I have migrated a set of SQL 2000 databases to SQL 2008. Most is working well, however I have some stored procedures that scheduled and run by SQL Server Agent jobs that are giving me troubles. Many of the scheduled stored procedures work, but the stored procs that access a database other than the default databases are failing with the following message:

Executed as user: XYZ\YadaYada. The server principal: "XYZ\YadaYada" is not able to access the database "MyOtherDatabaseOnSameServer" under the current security context. [SQL STATE 08004](Error 619) The step failed.

Obviously, I changed the names to protect the guilty.

The account is a user in all of the relavent databases and is a memeber of db_owner, db_datareader, and db_datawriter.

When I run these same procedures from a query window in SMS using the same accounts (I have tried many) they work fine.

What am I missing?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008