Saturday, February 25, 2012

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.

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

Thanks for the quick reply, but I'm not sure how to do what you are saying. I don't have a permissions script, and if "re-issue permissions manually" is done through the database properties dialog box, that is not working for me.

I have the same user login on both systems, but when I restore to the second system, it is not recognized as the same. I can understand that, but don't know how to fix it. Even when I don't have the same user login on the 2nd system, I can't remove the user login associated with the DB in the properties dialog box under Permissions. HTH. I really would like to understand the fix for this. Thanks!

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

I'm doing some searches for info on generating the scripts you described.

Can you point me to a web page with instructions on how to generate the scripts? Or can you post it here if it is not too lengthy? I'm new to SQL Server scripts. Thanks!

|||

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.

|||

Hmm. Still no go. I had the first server generate the following:

/* For security reasons the login is created disabled and with a random password. */IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'u_iptracker')CREATE LOGIN [u_iptracker] WITH PASSWORD=N'e? ?è 52ü? :??¨?n:3 #??w??VC*u= ', DEFAULT_DATABASE=[IPTracker], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOALTER LOGIN [u_iptracker] DISABLEGOUSE [IPTracker]GOIF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'u_iptracker')CREATE USER [u_iptracker] FOR LOGIN [u_iptracker] WITH DEFAULT_SCHEMA=[dbo]

But when I execute it on the second server, it runs, but I still get the same permission issue when I open the DB properties, Permissions, select u_iptracker and click Effective Permissions button. The error is:

"Cannot execute as the database principal because the principal "u_iptracker" does not exist, this type of principal cannot be impresonated, or you do not have permission. Error 15517"

The script has created a login for u_iptracker, but it is not mapped to the database. When I try to map it to the database, I get the following error: "User, group, or role 'u_iptracker' already exists in the current database Error: 15023"

So, I can't clear u_iptracker from the Permissions tab in the database properties, and I can't map the login u_iptracker to the database.

Score: SQL Server Express 2005 1 Me 0

What am I leaving out? Any help would be appreciated! Thanks!

|||

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!

|||

How to generate those scripts?

No comments:

Post a Comment