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?
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.
What is going on is there are two places userIDs live. One inside the user database and the other in master. When you restore to test from production, the link between the userID in the user database and the userID in master is broken. The link is called a Security ID (SID). Run the below script in the database that was restored and it will automatically fix all broken SIDs in that one database.
declare @counter int declare @lastuser int declare @sysuser varchar(100) declare @execvariable varchar(200) set @counter=3 select top 1 @lastuser=uid from sysusers where uid <16000 order by uid desc while @counter <= @lastuser begin select @sysuser=name from sys.sysusers where uid=@counter if exists(select name from master.sys.syslogins where name=@sysuser) begin exec sp_change_users_login 'auto_fix', @sysuser end else begin print 'The user '+@sysuser+' does not exist in the master.syslogins table' end set @counter=@counter+1 end