May 12, 2015 at 4:33 am
Hello,
We have quite simple SQL database sitting on Azure, we are developing it on the fly so no Dev/QA/Prod versions, is on SQL Express 2012
Sometimes we need to take a copy of the database and attach to a local server for further revision/development then replace the one on Azure.
Initially we used <detach - attach> and worked fine till we introduced users with limited accesses and custom db properties such as db_executor.
Now, after attaching, can not use the limited user to connect via odbc.
Reading forums I understood that user profiles need to be rebuilt, got some SQL but does not seams to work.
Any idea what I'm doing wrong? or any advise on what should be the correct steps to accomplish this?
Please note that this is on SQL Express 2012.
Thank you,
Paul
May 12, 2015 at 2:14 pm
hr.ppaul (5/12/2015)
Hello,We have quite simple SQL database sitting on Azure, we are developing it on the fly so no Dev/QA/Prod versions, is on SQL Express 2012
Sometimes we need to take a copy of the database and attach to a local server for further revision/development then replace the one on Azure.
Initially we used <detach - attach> and worked fine till we introduced users with limited accesses and custom db properties such as db_executor.
Now, after attaching, can not use the limited user to connect via odbc.
Reading forums I understood that user profiles need to be rebuilt, got some SQL but does not seams to work.
Any idea what I'm doing wrong? or any advise on what should be the correct steps to accomplish this?
Please note that this is on SQL Express 2012.
Thank you,
Paul
So you were able to attach the database to the new instance, but the users don't have the same access as they did on the live instance?
Are your users currently logging in using SQL login or AD? What did you try when creating the users on the new instance?
May 13, 2015 at 1:08 am
after attaching the database try to check for orphan users
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
May 15, 2015 at 6:13 am
[/quote]
So you were able to attach the database to the new instance, but the users don't have the same access as they did on the live instance?
Are your users currently logging in using SQL login or AD? What did you try when creating the users on the new instance?[/quote]
- Actually the problem was that I was not able to connect via ODBC with a user which was defied as logon user.
- User login is with SQL Authentication (usr/pass)
- Database brought the user somehow at DB level.
The step by step is like this
1. Developed the db on local machine
Created 2 users and 2 logins (usr_read, usr_write)
Created a role db_executor and assigned to usr_write
Created a role sp_create and assigned to usr_write
These are to limit accesses... just mentioning
2. Detached the db and copied to Azure
3. Upon attaching was inactive (icon was grey), had to change something to make it active
4. Users appeared in the database, I had to create the login user then assign schema, database, not remember everything I did.
5. Continued the development in Azure
6. Wanted to take it back to my machine
7. Detached from Azure
8. Copied to local machine
9. Attached using SQL Server Management Studio
10. Did not managed to connect with any user, no matter what settings i was doing.
Thank you.
May 15, 2015 at 6:15 am
Almighty (5/13/2015)
after attaching the database try to check for orphan usersWINNERS NEVER QUIT AND QUITTERS NEVER WIN
How do I do that please?
May 15, 2015 at 6:26 am
use the command on that database
sp_change_users_login @Action='Report'
GO
if you got any result those will the users who wont be able to access the database
then fix them with
EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO
for the complete article
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
sqlserversdba.wordpress.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply