SQL server DB restored.Login failed error

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

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.

This worked for me:

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

(substitute 'UserName' and 'LoginName' with the appropriate values for your setup)

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

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 ! :)

If it is a SQL Server-based login and you restored the database to a DIFFERENT SQL Server than where it was originally on, then palehorse's answer is the way to transfer logins. If the original SQL Server is not available, then you can create a SQL Server login on the new SQL Server and use sp_change_users_login to sync up the login and the user. Books Online covers how to do this.

If the login was a Windows domain account and you changed SQL Servers, then you should be able to just add the login to the new SQL Server and the login and user will sync up. The SID within SQL Srver for a Windows-based login comes from the SID of the account at the server or domain level.

If you restored back to the same SQL Server, then check to see if the default database for the login is valid. The login is stored in the master database, so restoring the database for the application shouldn't have affected that. That is unless you changed the login used to connect recently.

Otherwise, if you could post more about how you're logging into SQL Server and the exact error message, that may help diagnose the issue.

Check your connection string. (if its ASP.NET the web.config should have this). Verify the login (username/user id) exists on SQL server and has appropriate permissions on said database.

Seeing your replies to comments above, indicates that you've restored this from another server. That being said, as bnkdev stated check your connection string. Chances are you either have the wrong userid/password combo and/or the wrong instance name...assuming they were named differently for each server.

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

exec sp_change_users_login @Action='Report'

Then you can remap the users to the login with:

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

(substitute 'UserName' and 'LoginName' with the appropriate values for your setup).

I just finished dealing with this problem in my environment and in my case the problem was the target server was configured for windows authentication only which I found thru the sql server error log.

Check your error log! The error you get from the interface often contains insufficient info for security.