Permission Issue After Backup & Restore

Permission Issue After Backup & Restore

I have a system at work and 1 at home. When I backup the db from my work system and restore it to my home system, the user permissions are not working. I checked the properties of the database under the Permissions tab and I see Users, but when I click the Effective Permissions button, I get an error "Cannot execute as the database principal because "user_me" does not exist,..."

I check the Microsoft KB and got a hit, but that is for database ownership, not user permissions. I've had to work around this by creating a new user on my home system, but I would really like to figure out how to keep the same user name, etc. to keep the 2 systems the same. Thanks!

BTW, the SQL Server 2005 Express error message ID is 15517 and LinkId is 20476, but there is no page for this link when I click on it.


Solution:

When you back up and restore you have to rerun the permissions script if you have one or re-issue the permissions manually for the users you had earlier.

You can generate the scripts for creating the permissions from your souce server. compile/execute them on the second server.

I think I have the procedure. Select DB, right-click, Tasks, Generate Scripts.

I'll select the Users and permissions to generate. Thanks for your help! I'll post when I complete the task.

The final fix was to drop the user from the database properties dialog. That way the login on the server where I restored the database could be assigned to the restored database without the "user already exists" error.

Finally!

Score: SQL Server Express 2005 1 Me 1

I

l'll call it a night!