August 26, 2008 at 12:30 pm
I have been asked to create a test database from the production database. The only problem is there is no separate server for the test database. It will have to be created on the production server where the production database is located. My concern is that a user could inadvertently access the production database when they meant to access the test database. Since I don't have any other choice right now, what is the best recommendation to ensure that this doesn't happen?
August 26, 2008 at 12:35 pm
i dont know if you can create 2 database with the same name on the same server. you can create the database with differnet name and use different sql user account for different databases.
Another option is to create a separate instance of SQL server on that machine
August 26, 2008 at 12:41 pm
I will be calling the database a different name. If I put the test database under a different instance which I was thinking of doing, then I have to fix the user logins. I know I have to use sp_change_users_login but I'm not sure how to use it in this case. There are about 50 users that will have to be fixed.
August 26, 2008 at 12:58 pm
My concern is that a user could inadvertently access the production database when they meant to access the test database
Why would you want users to have access to both databases? Are all your users developers?
August 26, 2008 at 1:17 pm
as the server is the same then it will be a manual process.
So you have to give read only right for the producttion database and dbo rights for the test database.
August 26, 2008 at 1:18 pm
No they are not all developers. I have queried the application team to determine if all the 50+ users need to been defined and will go from there.
Do have any suggestions on how to use the sp_change_users_login when you don't know the password for the user and the master db is different when under a separate instance?
August 26, 2008 at 2:05 pm
>>My concern is that a user could inadvertently access the production database when they meant to access the test database. Since I don't have any other choice right now, what is the best recommendation to ensure that this doesn't happen?
Is a web application?? If so, then you'll have a myapp.com and test-myapp.com. Give them two different bookmarks and put in massive red letters in the header/icon for the test system: TEST SYSTEM. Perhaps change the background color with .css to signify different environments.
You can only do so much.
Or make separate logins as someone else suggested.
August 26, 2008 at 2:07 pm
Patricia Johnson (8/26/2008)
No they are not all developers. I have queried the application team to determine if all the 50+ users need to been defined and will go from there.Do have any suggestions on how to use the sp_change_users_login when you don't know the password for the user and the master db is different when under a separate instance?
Is your goal to retain the same password in test and prod?? There is a way to match them up by taking the hash out of the master db of the source server and putting that in the destination.
You might want to make the passwords different as another way to ensure that people don't accidentally get into test and start working.
August 26, 2008 at 3:07 pm
Is it advisable to have the same passwords of Production system for the test system....
August 26, 2008 at 3:48 pm
Hey all, what the application team decided to do is have me delete all the users except two. One user is an admin type account and the other is test account that only has access to the test database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply