I recently migrated one of the databases I support from SQL Server 2000 to SQL Server 2005 using backup/restore. I didn't have any errors or issues until the time when the application that uses this database tried to execute a stored procedure. The application failed and once I ran a sql profiler, I determined that the reason it failed because the user that tried to execute this sp didn't have execute permission. This user is in the database role. When I compared this database role's permission to database role's permission in SQL Server 2000, then this database role has an execute permissions on this stored procedure in SQL Server 2000. I am not sure what went wrong but somehow the permissions that were assigned to this database role didn't carry over to SQL Server 2005.
have u migrated all the logins with permission from SQL server 2000 to 2005 machine ? Backup / restore does not migrate all the permissions only data? Because it would have got mismatched i mean database user and corresponding login due to db id change.Refer microsoft link for transfer SQL server logins