Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mtortolano
Starting Member
10 Posts |
 Posted - 2006-04-30 : 06:53:57
|
| I'm working with a SQL Server 2005 DB backup from my online hosting company. I want to install it in SQL Server 2005 locally to run an offline copy of the website for development but I'm having a problem.The only way to get the data locally is to use a .bak file and Restore it to a DB on my local system. That works fine.The problem is that the DB defines a User (let's call it WebAdmin) but there is not local Login associated to it (also called WebAdmin).I can manually create the appropriate Login and passwd locally but I can't seem to figure out a way to associate it to the User.1. If I try from the User properties screen (in SQL Management Studio) in the DB itself the "User Name" section with the bit to assign the login is greyed out.2. If I try from the Login properties screen (under the "User mapping" settings) I get this error:User, group, or role 'WebAdmin' already exists in the current database. (Microsoft SQL Server, Error: 15023)3. If I try making sure a Login with the right name is present when I restore the DB it makes no difference it doesn't associate it.4. If I try deleting the User "WebAdmin" to remake it with the right login details it won't let me because the Schema, Roles etc. etc. are assigned to it!I should add that I need to use this User and SQL Server authentication as I'm trying to keep the DB is a suitable state for then uploading back to the Website.Anyone know how to solve my snafu?Thanks,Michael. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-30 : 08:32:01
|
| I suspect that the problem is that the Login on your Server has a different ID number from the one in the restored database (i.e. the ID number on the Server that the backup was made on)You should be able to create a login for the user on your server - e.g. using:EXEC sp_addlogin 'MyUserName', 'MyPassword', 'MyDatabaseName' and then "reconnect it" to the orphaned user in the database:EXEC sp_change_users_login 'Update_One', 'MyUserName', 'MyUserName'Kristen |
 |
|
|
|
|
|
How to assign a login to an existing user?