Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2013-02-03 : 22:56:56
|
| 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
52326 Posts |
Posted - 2013-02-03 : 23:44:15
|
| 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-04 : 05:43:19
|
| 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. |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2013-02-04 : 22:05:12
|
| 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 |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-05 : 01:54:39
|
| Yes, please run the following sql on prod side..........it will list all the logins with encrypted passwords SET NOCOUNT ONSELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''',', @defdb = ''' + dbname + '''',', @deflanguage = ''' + language + '''',', @encryptopt = ''skip_encryption''',', @passwd =', cast(password AS varbinary(256)),', @sid =', sidFROM sysloginsWHERE name NOT IN ('sa')AND isntname = 0Each 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. |
|
|
|
|
|
|
|
|
After Refresh TEST DB How to Repair Logins ?