November 20, 2017 at 7:14 am
Hello,
I have 2 instances.
1 for production and 1 for QA.
i backed up 1 of my db in production and restore it on my QA instance but it didn't restore my user & credentials.
What am i doing wrong?
Thanks in advance!
November 20, 2017 at 7:18 am
Logins are stored in the master databases and are not copied on a backup and restore.
What is copied is the mappings between logins and users.
I would recommend you get sp_help_revlogin, run it on your prod server and copy the output to your QA server to recreate the logins.
November 20, 2017 at 7:21 am
89netanel - Monday, November 20, 2017 7:14 AMHello,
I have 2 instances.
1 for production and 1 for QA.
i backed up 1 of my db in production and restore it on my QA instance but it didn't restore my user & credentials.
What am i doing wrong?Thanks in advance!
A back up of a database will contain all the user details for that database. When you restore it, it will (again) contain those credentials.
Don't forget that logins (these are DIFFERENT to users) are server specific (their details are contained in the master database). If you are using SQL logins, then your SQL Login will very likely have a different SSID on your two different instances, so the users may not be mapped to the respective logins. If you're using AD, however, then the SSID will be the same on both servers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 20, 2017 at 7:29 am
Hi anthony.green and Thom,
Thanks for your quick reply!!!
i didn't mention that i use azure as SAAS and not as PAAS
Is it still possible to use sp_help_revlogin in order to export and import my logins ?
or maybe there is a different way for that?
I have both "Azure SQL login" and "AD integrated login"
Thanks in advance!
November 20, 2017 at 8:21 am
Not that clued up on Azure as I work on government projects which we cant use the cloud, but does the following link help at all https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy
November 20, 2017 at 10:49 am
89netanel - Monday, November 20, 2017 7:29 AMHi anthony.green and Thom,
Thanks for your quick reply!!!
i didn't mention that i use azure as SAAS and not as PAASIs it still possible to use sp_help_revlogin in order to export and import my logins ?
or maybe there is a different way for that?I have both "Azure SQL login" and "AD integrated login"
Thanks in advance!
Nah, it's still a VM here (PaaS is just a database, no backups or restores available, so since you're doing a backup). It's the issues and solutions already identified.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply