| Author |
 Topic  |
|
|
jbates99
Constraint Violating Yak Guru
285 Posts |
 Posted - 02/03/2013 : 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
India
47173 Posts |
Posted - 02/03/2013 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
srimami
Posting Yak Master
152 Posts |
Posted - 02/04/2013 : 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
285 Posts |
Posted - 02/04/2013 : 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
152 Posts |
Posted - 02/05/2013 : 01:54:39
|
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. |
|
|
| |
Topic |
|
After Refresh TEST DB How to Repair Logins ?
