Re: Moving a database to another server

tnt (tnt@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I needed to grab the database home and work on it. So I restored it
onto a server at home.

The database has a user (lets called it X) that has public, db_owner,
db_datareader, & db_datawriter.

On the security (logins), there is no user X. How do I go about in
specifying the same user from the database? I also don't know the
password for that user.

Is it something to do with the orphaned stated earlier?

Exactly. In SQL Server there are principals on two levels, server and
database. A server principal is also known as a login and it can be an
SQL Server login or a Windows login. This login may have access to one
or more databases, in which he is known as a "user" or a database principal.

If you move a database from one server to another, there is nothing that
says that a database principal will have a matching server principal. If
the database user is derived from a windows login, and the database is
moved from one server in a domain to another, chances are good. Or at least,
if the underlying Windows login has been granted access to both servers,
there will be a match.

On the other an the likelyhood that an SQL login will map correctly after
a restore is small, because the mapping is done with a SID, not by
name.

And if you move a database outside of the domain - which I assume you
have in your case - the likelyhood for a match is about nil, no matter
if it is an SQL login or a Windows login.

If this particular user comes from an SQL login, it's easy:

CREATE LOGIN youruser WITH PASSWORD ='whateveryoufeellike'
go
USE themoveddb
go
ALTER USER youruser WITH LOGIN = youruser

If the orphande user comes from a Windows login (you can tell from the
backslash), you would have to add that Windows login on your machine,
which may be kind of difficult if the machine is not part of the original
domain.

But you can still execute as this user, by simply saying:

EXECUTE AS USER = 'youruser'

and it will be as you have logged in as that user. Almost. If you need to
do things outside the database, there will be restrictions. To become
your original self, use the REVERT command.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Moving a database to another server
    ... onto a server at home. ... The database has a user that has public, db_owner, ... SQL Server login or a Windows login. ...
    (microsoft.public.sqlserver.server)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
Loading