Test dastabase on production server

Question

Test dastabase on production server

Patti Johnson

SSCertifiable

Points: 7054
More actions
August 26, 2008 at 12:30 pm

#154662

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Masood Ali-854067 SSC Enthusiast Points: 190 More actions · Answer 1

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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 2

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.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

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?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Masood Ali-854067 SSC Enthusiast Points: 190 More actions · Answer 4

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.

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 5

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?

Sam Greene SSCertifiable Points: 5252 More actions · Answer 6

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

Sam Greene SSCertifiable Points: 5252 More actions · Answer 7

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.

babloo.sqlnut SSC Enthusiast Points: 193 More actions · Answer 8

Is it advisable to have the same passwords of Production system for the test system....

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 9

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.