Re: Moved database to new server...before creating logins

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 11/11/05 

Date: Fri, 11 Nov 2005 11:08:35 -0500

Sure there are several articles here that cover this and more. But at the
least you can just create a new Login and then use sp_change_users_login to
map the user to the login.

http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server 

-- 
Andrew J. Kelly  SQL MVP
"Brent White" <bwhite@badgersportswear.com> wrote in message 
news:1131724756.657385.127960@f14g2000cwb.googlegroups.com...
>I feel like an idiot for this.
>
> We are moving our SQL Server database (2000 Standard) to a new server
> (also 2000 standard--don't worry, we've got a separate license for it).
> What I did was backup the database on the old server and restore it to
> the new server for testing our software application.
>
> Only problem is, I didn't create the users beforehand, so now I can't
> log into the new server with the login.  It says I can't access my
> default database.
>
> WIthout dropping the database, is there a way I can fix this?  It would
> be easy enough to drop and re-restore the database, but if there is a
> shorter way that would also work just as well, please enlighten me.
>

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Access 97 to a more powerfull platform!?
    ... Moving to SQL Server is common when a database "outgrows" ... Design problems are far and away the usual reason why a database ...
    (microsoft.public.access.gettingstarted)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)