I took a backup of our production database and restored it to both the test and development environments last night, but this morning a single user is not able to login to the test/dev DBs. I can go into SSMS and connect to SQL Server with the user but when I try to connect to the specific databases I am given a 4064 login failed error. The credentials should be correct as I can turn around and login to production and run queries with no issues. I have compared both permissions sets and did not see any difference between prod and dev/test. They both have "connect" permission to the database schema and they are both part of the "db_reader" and "db_executor" groups. What might I be over looking in the test/dev system that is preventing this user from connected to the Database?
SQL Server – User Cannot Login After Restoring DB to Dev and Test Environments
authenticationsql serversql-server-2012
Related Question
- SQL Server – Restoring Database with Data Compression
- SQL Server 2016 – SSISDB User ‘AllSchemaOwner’ Not SQL Server Login
- Oracle to SQL Server Linked Server Data Type Discrepancy
- Sql-server – Remote execution of SQL Agent job
- SQL Server – Production Database Substantially Bigger Than Dev
- Sql-server – MS SQL 2008 R2 – How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another
Best Answer
Have you tried running
EXEC sp_change_users_login @action='Report';
to see if they are orphaned for that database?If there is a result, you can run with the Auto_Fix option to remap the user.