Last post Nov 08, 2007 11:10 PM by rfurdzik
Mar 22, 2007 02:27 PM|Bohunkus|LINK
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.
Mar 22, 2007 03:13 PM|ndinakar|LINK
Mar 22, 2007 04:23 PM|Bohunkus|LINK
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!
Mar 22, 2007 05:02 PM|ndinakar|LINK
Mar 22, 2007 06:13 PM|Bohunkus|LINK
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!
Mar 22, 2007 06:18 PM|Bohunkus|LINK
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.
Mar 22, 2007 06:45 PM|Bohunkus|LINK
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'ðÎûÈ52Ü:¨n:3#wÑ¡VC*u=', DEFAULT_DATABASE=[IPTracker], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
ALTER LOGIN [u_iptracker] DISABLE
IF 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!
Mar 22, 2007 11:58 PM|Bohunkus|LINK
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.
Score: SQL Server Express 2005 1 Me 1
l'll call it a night!
Nov 08, 2007 11:10 PM|rfurdzik|LINK
How to generate those scripts???