Change sql server 2008 r2 database name/schema and username

I have two accounts at Softsys Hosting with a domain on each account. I need to clone the database from one domain to the other, so I can run one as a staging site and the other as the live site.

I've taken a back-up of the live database and tried to restore it to the dev account, but it fails because of database/schema/user name conflicts (both databases will be on the same server apparently so it won't let the 'same' database be created 开发者_如何学运维twice, which makes sense).

Softsys said that I need to take a local back-up of the database, change the name, schema name and user name, then upload that to my dev environment. The trouble is, I just can't get it to work. When I restore the back-up to my local system it won't let me log-in via the same username/password as on the live server - I can only seem to log is as 'sa'.

Can someone please explain to a database dunce, how I can get this .bak file from the live database running on my local machine with a working user account, and also how to change the names of the schema/user/database so that I'll be able to upload it to the dev server without any conflicts with the live domain?

I'm using MSSQL 2008 R2 and SQL Server Management Studio 2008 R2 Express.

Thanks all...

The reason you cannot use the login from your Prod box on your Dev box is that the login does not exist on the Dev box.

When you move the database over, it sounds like you only have the SA login on the Dev box.

You should script out the login(s) that you use on the prod server and recreate them on your Dev box. Read up here http://support.microsoft.com/kb/246133

If you have the login on your dev box, or you just created it with the script above, you are going to have to connect the Login on the server to the User in the datbase.

You need to link the the new Login on the server to the User in the database. Use the ALTER USER statement to fix this. http://msdn.microsoft.com/en-us/library/ms176060.aspx

This used to be done with sp_change_users_login http://msdn.microsoft.com/en-us/library/ms174378.aspx