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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 After Refresh TEST DB How to Repair Logins ?

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

Go to Top of Page

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

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

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 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
   

- Advertisement -