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?


2 Answers 2


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.

  • 3
    remember that sp_change_users_login is deprecated . Instead alter user user_name with login_name is a recommended way.
    – Kin Shah
    Commented Dec 2, 2016 at 15:47

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
select  @sysuser=name from sys.sysusers where uid=@counter 
            if  exists(select name from master.sys.syslogins where name=@sysuser)
            exec sp_change_users_login 'auto_fix', @sysuser
            else begin
            print 'The user '+@sysuser+' does not exist in the master.syslogins table'  
set @counter=@counter+1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.