SQL Server 2005: Restore database with different users/logins

In brief, I have a scenario where I want to restore a database backup as a different database, plus also change the owner of the objects and the logins that are associated with the new database.

In more detail:

I have a database called cyber.  I have two logins: cyber and cyber_report.  In addition to the normal users (eg: dbo, guest, etc) the cyber database has two users: cyber and cyber_report.  These two users are associated with the respective logins of the same name.  Also, there are two schemas: cyber and cyber_report.  The main difference between the users cyber and cyber_report is that cyber user owns all the tables, stored procs, etc.  The cyber_report owns a bunch of views which are used by the reporting engine (Crystal Reports).  The application for which this database is the back end connects using the cyber user.

I want to backup the cyber database, and restore it as cyber_dev.  Just like above, there will be two logins: cyber_dev and cyber_dev_report.  The cyber_dev database needs to have two users and two schemas: cyber_dev and cyber_dev_report.

The straight restore part I can do without too much drama.  But when I do, all the tables etc are still owned by cyber (not cyber_dev).  I want to be able to either some how "map" the old logins/users/schemas to the new ones; or alternatively run a script after the restore which "fixes" the new database.

This is somewhat urgent - please help!

Cheers,

Paul Hobbs
mrgordonzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
That will be a lot of work.  You need to create the two new owners, create the two new schemas, rename each object from the old schema to the new schema, remap the new users to the old logins, drop the old schemas and drop the old users.  

And for what?  It adds no additional security and makes promoting programs out of dev an order of magnitude more difficult because the schema name changes between environments.  Push back on whoever came up with the idea.
0
mrgordonzAuthor Commented:
Hi dqmq,

What is a better solution then?  I need to be able to have the two databases in the same instance of SQL Server 2005.  Each database is being accessed by a separate instance of the application (which runs on a separate app server).

This was my idea, so unfortunately there isn't anyone to push back on. :-)

I am a bit rusty on SQL Server - the past few years I have worked mostly with Oracle.  How does one backup and restore a database as a new database (in the same instance of SQL Server) without running into the problem of mapping users, logins and schemas?
0
dqmqCommented:
Just restore it with a different database name.   Then you will have objects named like this.

original database
----------------------
yourserver.cyber.cyber.yourtable
yourserver.cyber.cyber_report.yourview


new database
---------------------
yourserver.cyber_dev.cyber.yourtable
yourserver.cyber_dev.cyber_report.yourview


Everything within the database will be named the same and database security will all be the same.   But the cyber and cyber_report logins WILL NOT HAVE access to the new database.  You will need to grant access or create a different set of logins that have access.


0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mrgordonzAuthor Commented:
But that is precisely the problem:

In the new database it needs to be:

yourserver.cyber_dev.cyber_dev.yourtable
yourserver.cyber_dev.cyber_dev_report.yourview

(I assume it works like this: server.schema.user.object)

I did as you suggested, but for all the tables the schema remained cyber; it didn't change to cyber_dev.  Same goes with stored procs, functions, views, etc.
0
mrgordonzAuthor Commented:
Here is the script I used to restore the database:

RESTORE DATABASE cyber_dev
FROM  DISK = N'D:\mssql_data\backup\cyber.bak'
WITH  FILE = 1,  
MOVE N'cyber_data' TO N'D:\mssql_data\cyber_dev_data.mdf',  
MOVE N'cyber_log' TO N'D:\mssql_data\cyber_dev_log.ldf',  
MOVE N'sysft_cat_search' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\cat_search_cyber_dev',  NOUNLOAD,  REPLACE,  STATS = 10
GO

I then manually created the new logins, schemas and users (cyber_dev and cyber_dev_report).  Thus far I have been running a variety of scripts to change the schema for tables, views, stored procs, etc.  Seems to be working, but it is tedious.  This is sooooo much easier in Oracle.
0
dqmqCommented:
>I assume it works like this: server.schema.user.object

no, it's like this:  server.database.schema.object

>I did as you suggested, but for all the tables the schema remained cyber; it didn't change to cyber_dev.

That's correct. And that's how I would prefer it.

Consider one table, originally called:

    server.CIBER.ciber.customer

You connect to server, do a "USE CIBER" (the database) and reference ciber.customer (schema.table) in your code.

You have a copy in the CIBER_DEV database on the same server.  You connect to the server, do a "USE CIBER_DEV" and still reference ciber.customer in your code. See, this is very important: the same code works in both environments.

If the schema name changes between environments, then all the references to it also need to change when you migrate code.  I know some places do it that way, mostly by avoiding references to the schema name and there are other compromises, as well.

In other words, it's easier to maintain separate databases for each environment than it is to maintain separate enviroments within one database.  I've found the same to be true in Oracle.  





 








0
mrgordonzAuthor Commented:
Hi dqmq

Thank you for all your help and advice - I will carve out some time to see if I can get the application to work by doing things as you have described.  

I think part of the issue for me is that I am used to schema being one thing in Oracle, but it seems to be slightly different in SQL Server 2005.  I have found that using the Oracle datapump I can import a schema, and use the remap_schema parameter to ensure that all the objects sit in the correct schema.  So in a single Oracle database I have lots of different schemas, each for separate instances of the application.  

Where I am getting a little confused is that in SQL Server you have lots of separate databases, and now in 2005 they have introduced schemas.  I don't quite see the point of schemas if you have separate databases anyway.  I last worked on SQL Server 2000 a few years ago so I think I have some catch-up reading to do.
0
dqmqCommented:
Indeed, schemas have changed dramatically for the better with SQL 2005.  They provide a level of object ownership that was not possible before and really do simplify administration if used properly.  Consider that you cannot  drop a user if that user owns a single object.  With 2000, that involved changing ownership of a myriad of tables, views, procedures and other objects.  With 2005, it's simply a matter of changing schema ownership.

Consider a database with these schemas:
  dbo
  sys
  ciber
  ciber-reports

Then, you want a development environment and do like this:
   dbo
   sys
   ciber
   ciber-reports
   ciber-dev
   ciber-reports-dev

See what has happenend? You do NOT have isolated environments, as the dbo and sys schemas are now common to both.   How do you test a change in the dbo schema? Add dbo-sys, as well?   No, it is far easier to create a second, fully parallel database which starts out as a copy of the first, including exactly the same schemas.

In Oracle, it's common for each developer to have their own schema in the development environment.  You can do that in SQL 2005, as well, but then (and just like Oracle), you need a tool to migrate object-by-object to a common schema for integration tests.  At the end of the day, only the common schemas migrate back to production.














0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.