Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 After Refresh TEST DB How to Repair Logins ?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

396 Posts

Posted - 02/03/2013 :  22:56:56  Show Profile  Reply with Quote
I have refreshed a test db from production on a different instance.

Now some logins are failing in Test. Do I need to drop all the logins in Test and run a script from production to recreate all logins?

Thanks, Jack

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/03/2013 :  23:44:15  Show Profile  Reply with Quote
what we do is just opposite as test may have additional logins or additional priviledges for some logins. what we do is script out logins with permissions before refresh and reapply them once refresh is done from prod.

SQL Server MVP

Go to Top of Page

Posting Yak Master

160 Posts

Posted - 02/04/2013 :  05:43:19  Show Profile  Reply with Quote
As you have already refreshed the database, you can delete the failed logins on test db and run the scripts to generate the same users/passwords. Do let me know if you need scripts to execute the same usernames/passwords as that on prod servers.
Go to Top of Page

Constraint Violating Yak Guru

396 Posts

Posted - 02/04/2013 :  22:05:12  Show Profile  Reply with Quote
Thanks for your replies visakh16 and srimami.

Yes,the damage has been done, test was already refreshed.

Srimami, does your script do anything more than just scripting out all the logins on the production side, then execute the script against the test database?
Please let me know. And thank you.
Go to Top of Page

Posting Yak Master

160 Posts

Posted - 02/05/2013 :  01:54:39  Show Profile  Reply with Quote
Yes, please run the following sql on prod will list all the logins with encrypted passwords
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0

Each row in the column list is a column in the result. The script uses the isntname column to ascertain whether a login is a SQL Server login or an NT login. Executing the above code in a Query Analyzer grid shows that the binary data (and other parameters) are in separate columns. Also, because the password column is in Unicode (and encrypted), the code converts the password column into VARBINARY (256), so that you don't lose characters.

Copy the entire row for each login and execute it on test db to retain the login with same username and password as that of prod.

Please do let me know if you face any issues need additional help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000