Database Role Migration

Hi All,

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.

Any ideas?

Are logins mapped properly to database user?. You can run sp_change_users_login 'report' to see if there are any mismatched logins.

Did you copy sql logins used from sql2k to sql2k5? If so, you can remap sql login to db user with 'sp_change_users_login auto_fix, user_name'.