SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

jbates99
Constraint Violating Yak Guru

370 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

visakh16
Very Important crosS Applying yaK Herder

India
52309 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
http://visakhm.blogspot.com/

Go to Top of Page

srimami
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

jbates99
Constraint Violating Yak Guru

370 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.
Jack
Go to Top of Page

srimami
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 side..........it will list all the logins with encrypted passwords
SET NOCOUNT ON
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  
 New 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.08 seconds. Powered By: Snitz Forums 2000