SQL server bit restored. Error login failed

Question

I restored one db back to my SQL server. I have an ASP program that accesses db. When I run this, I get a login error for this user. What exactly should I do for this?

Answer 1

It is likely that the login, even if it appears on a new server, is not the same. Login information is stored with a SID that is unique (usually) to each server. There are ways to translate the SIDS login ID if necessary, but you can also simply grant permissions to the new user / user of the new server on the objects in your database.

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

Answer 2

The user database is different from logging into SQL Server. A Login has access to the server and in almost all cases is also associated with user accounts that have certain privileges for one or more databases. What happened in your case is that the User is still present in the database (because he was there when you restored db), but does not exist - like Login - on the server. That is, there is no entry with them, since logins are not restored when restoring the database.

When I move the database to a new server, I usually just delete the user from the restored database and then create a Login. To do this, first create your Login in the "Security" section at the database level. Then simply use the User Mapping page in the Login Setup dialog box to automatically create a linked user account in any database for which it is suitable. Here you will also assign certain rights.

However, there is one delay: you cannot remove a user from the database. This happens when the user “owns” the schema or other objects in the database. In this case, you will either have to use the Palehorse link for further instructions (I forgot the commands from the top of my head), or you will have to reset to use the schema / objects to use dbo.

Answer 3

If this is a SQL Server-based login and you restored the database to DIFFERENT SQL Server than where it was originally included, then the answer to paleontology is a way to transfer logins. If the source SQL Server is not available, you can create a SQL Server account on the new SQL Server and use sp_change_users_login to synchronize the login and user. The book "Internet Books" describes how to do this.

If the login was a Windows domain account and you changed the SQL servers, you just need to add the login to the new SQL Server, and the login and user will be synchronized. The SID in SQL Srver for Windows-based login comes from the SID of the account at the server or domain level.

If you return to the same SQL Server, check to see if the default login database is valid. Logon is stored in the main database, so restoring the database for the application should not have affected this. That is, if you have not recently changed the login used to connect.

Otherwise, if you could provide more details about how you log in to SQL Server and the exact error message, this can help diagnose the problem.

Answer 4

This worked for me:

ALTER USER *userName* WITH LOGIN = *loginName* ALTER USER *userName* WITH DEFALT_SCHEMA = *schemaName* 

(replace "UserName" and "LoginName" with the appropriate values ​​for your installation)

This is the same as sp_change_users_login, I think, but the dox note: sp_change_users_login will be removed from future versions of SQL Server and use ALTER USER instead

Answer 5

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

When you get 'Login failed for user' oldUser '. because it was not possible to open the explicit database.

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

Answer 6

Check the line. (if its ASP.NET should have this web.config). Verify that the login (username / username) exists on the SQL server and that it has the appropriate permissions in the specified database.

Answer 7

Seeing your comments on the comments above means that you restored it from another server. At the same time, as indicated in bnkdev, check your connection string. Most likely, you either have the wrong combination of userid / password and / or the wrong instance name ... provided that they were named differently for each server.

Answer 8

First check to see if you have orphaned users with the following stored procedure:

 exec sp_change_users_login @Action='Report' 

Then you can reassign users to login with:

 exec sp_change_users_login @Action='update_one', @UserNamePattern='UserName', @LoginName='LoginName' 

(replace "UserName" and "LoginName" with the appropriate values ​​for your installation).

Answer 9

I just finished working with this problem in my environment, and in my case the problem was that the target server was configured only for Windows authentication, which I found through the sql server error log.

Check the error log! The error you get from the interface often contains insufficient security information.