October 7, 2008 at 1:31 pm
I'm migrating databases from SQL2000 to SQL2005 and this is what I've been doing.
1) Create the users on SQL2005
2) Restore database on SQL2005
3) Confirm the permissions
This has worked for most of the databases except for a few. For example I have one that has a user we'll call UserX. I create UserX on 2005, restore the database and UserX under the main security doesn't have dbowner access to the DB. If I try to give UserX access it will error out and say username already has access in the database. If I look at security under the database, UserX will be listed. If I deleted UserX from there, I can go into the main security and grant UserX dbowner access. There are times when I cannot delete UserX with the error it's associated with a schema. The only way I can get around this is to make another account the member of the schema then deleted the use. Am I doing this wrong? If so, what's the proper procedures? Any help would be appreciated. Thanks.
October 7, 2008 at 1:45 pm
User mapping in the database is based on uid not the name so you should not get the same uid on a new server. You can use sp_change_users_login to fix this issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 7, 2008 at 2:57 pm
Per Microsoft, "Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead." Can you give me the syntax to do this with ALTER USER? Thanks.
October 7, 2008 at 3:07 pm
Try this:
ALTER USER user_name WITH LOGIN = login_name
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply