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 |
|
Kareem Ayoub
Starting Member
2 Posts |
 Posted - 2008-01-08 : 09:37:34
|
| Here is the problem in details :It’s a classical ASP ( not .net ) website and it has a sql server 2000 backend database. I’m moving the website to a new windows 2003 server with sql server 2005 standard edition.The hosting company ( Godaddy ) of the website refused to give me the mdf & ldf files for security reasons so they only allow to give .bak files.I restored the database to sql server 2005 using the .bak file , the sql server has SQL server authentication, and everything seemed to be nice.The problem is : The database has a user name and he is the owner of the database schema. When I try to login with it the sql server says invalid login.How to change the PASSWORD of this user after the backup?I tried other scenarios to solve the problem but with no hope…. Some of them were:• I added a new user to the SERVER security node, and I mapped it to the database schema, it worked fine BUT : all SQL statements in the code are like “ SELECT * from table” not “SELECT * FROM scehma.table”… so I don’t think the user was mapped correctly?• I tried to delete the old user, but sql server said that he is the owner of the schema.The only solution I have now is to change all sql statements in the code to be like “SELECT * FROM schema.table “… which will be a GREAT PAIN. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-01-08 : 10:31:14
|
| You will need to use sp_change_users_login (Usage in Books Online). SQL Server security comes in two parts. First is the login, which lets you into the server. Then there is the user, which is your name in any particular database. So logins can exist without users, and as you have seen, users can exist without logins. The login you created simply has a different SID than the user, so the two do not "see eye to eye". After you run the procedure above, the user's SID should be changed to the SID of the login, and all should be right again. |
 |
|
|
sql_noob
Starting Member
9 Posts |
Posted - 2008-01-08 : 11:44:29
|
| after a lot of pain i have a saved sp_change_users_login script with almost every login we have to make this go faster |
|
|
|
Kareem Ayoub
Starting Member
2 Posts |
Posted - 2008-01-08 : 12:31:29
|
| I thank the both of you.Problem fixed :) |
|
|
|
|
|
|
|
|
SQL login after restoring .bak file