Database Role Migration

[inet]

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?

[skhanal]

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

[rmiao]

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