SQL server DB restored.Login failed error

Question

I have restored one db back up to my SQL server. I have an ASP program which is accessing the db.When i am running this ,i am getting an error login failed for this user.What exactly i have to do for this ??

Answer 1

It is likely that the login, even if it extists on the new server, is not the same. Logins information is stored with a SID that is unique (normally) per server. There are ways to transfer the login SIDS if necessary but you can also simply give permissions to a new login/user on the new server to the objects in your database.

Here is a link to transfering logins. http://support.microsoft.com/kb/246133

Answer 2

A database User is different from a SQL Server Login. A Login has access to the server and, in almost all cases, is also linked to User accounts having specific privileges for one or more databases. What has happened in your case is that the User is still present in the database (because it was there when you restored the db) but does not exist - as a Login - on the server. That is, there is no Login associated with them since logins are not restored with a DB restore.

When I move a database to a new server, I usually just drop the User from the restored database and then create a Login. To do this, you'll first create your Login under the "Security" tab at the DB level. Next, just use the "User Mapping" page in the Login setup dialog to automatically create an associated User account in any database for which it is appropriate. This is also where you'll assign specific rights.

However, there is one hitch: you may not be able to drop the User from the Database. This happens when the User "owns" a schema or other objects in the database. In this case, you'll either have to use Palehorse's link for further instructions (I forget the commands off the top of my head) or you'll have to reset the schema/objects to use dbo instead.

Answer 3

FROM http://justgeeks.blogspot.com/2010/02/get-login-failed-for-user-after.html

When you get 'Login failed for user 'oldUser'. because Failed to open the explicitly specified database.

Just execute this sp_change_users_login 'Update_One', 'oldUser', 'oldUser' and Voila ! :)