After database restore, cannot set a login for access to database

From: Stew S. (sschectman_at_batesww.com)
Date: 07/31/03 

Date: Thu, 31 Jul 2003 14:54:40 -0700

After restoring a database backup to another server, I
want to set a login (either Windows or SQL authentication)
to an existing user in the database that happens to be the
owner of all the objects in the database.

After the database restore, that user ('staging') has an
empty 'login name'. When I create a new login by the same
name as that existing user, and then try to select the
database access permissions, I get the error:
"Error 21002- [SQL DMO]User 'staging' already exists"

There is an MS Knowledge Base article, #819261, for bug #
363065, that suggests a workaround that involves a New
User and login.

I think the problem with this workaround, for me, is that
it seems that after connecting with the new user, all my
existing queries have to have the owner-name qualifying,
as a prefix, any object references.

For example: query 'select * from employees'
 will get an error: "Invalid object name 'cladmineventlog'"
, but query: 'select * from staging.employees' will be ok.

It is not acceptable to have to use this kind of
qualifying prefix in queries for this restored database.

Thanks for any ideas.

Relevant Pages

  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ... a certain visitor in the Session, and use that value to start the right ...
    (comp.lang.php)
  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... the postgres user they are logged in as to one that can make ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ...
    (comp.lang.php)
  • RE: How to allow users to change their password?
    ... be set up to provide the Security dialog window for password changes. ... I'll have to login using their login ... > name/password first. ... See http://www.QBuilt.com for all your database needs. ...
    (microsoft.public.access.security)
  • Re: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Enter user name, enter password, then press enter...
    ... What do you do when the customer asks that the database login in be the same ... username and password to use all of the stuff that she is permitted to use. ... I have implemented this type of security for Access, VB, and Web Based ASP ...
    (microsoft.public.access.forms)