Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live and replicated to that instead so I must have set the job up incorrectly. The log shows that the table was created on live by the job.

For one or more reasons the copy job failed and the database it created was uloaded and apparently deleted (I can't find the .mdf or .log files) and this I could just about live with.

However after the failure of the job, all the object permissions were dropped from the live database so the accounts our application use to access the db were unable to connect.

After a restart the databases are running fine, but the permissions remain dropped.

I'd like to understand the reasons behind this behaviour and gather any dos and don't for the next time I have to do something like this.

Can anyone help?

share|improve this question
Do you still have a copy of the package? – Eric Humphrey - lotsahelp Mar 8 '11 at 14:15
Yes - shall I post it up? – 5arx Mar 8 '11 at 14:49

1 Answer

up vote 1 down vote accepted

The DB logins from live will overwrite your local DB logins. So, the local DB's logins, which contain the application user may be wiped out, unless it exists in the live DB also. Sometimes, the mappings between the DB user and the schema will get messed up. You can then recreate the user or use sp_change_users_login to fix the DB user mappings.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.