Stored Procedure Permissions Problem

Question

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?

Answer 1

things you might take a look at:

• who is the owner of the job?
• under which user is the sql-agent user running?

Answer 2

This looks like the cross-database ownership chaining issue.

Check that:

• the SQL server allows cross-database ownership chaining
• each database involved has cross-database ownership chaining enabled.
• User XYZ\YadaYada is a registered user of MyOtherDatabaseOnSameServer.
• The owner of the stored procedures is the same as the owner of the objects held in MyOtherDatabaseOnSameServer.

Hope that helps.

Answer 3

When you migrated the users, did you set up the users again or simply take what they had from the restore / attach operation?

You might need to run exec sp_change_users_login 'Auto_Fix', '<username>' on the database(s) in question where <username> is the actual name of the user.