0

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

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.

|improve this answer|||||
  • 3
    remember that sp_change_users_login is deprecated . Instead alter user user_name with login_name is a recommended way. – Kin Shah Dec 2 '16 at 15:47
0

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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