Restoring a database and losing users

Results 1 to 2 of 2

Thread: Restoring a database and losing users

  1. #1
    Join Date
    Dec 1969

    Default Restoring a database and losing users

    I backed up and restored a database successfully except for the users. The only user that shows is dbo, but I can&#039t add a user with the proper user name from my old server; I get the error that the user or role already exists in the current database.<BR><BR>When I go to role, name of public, there is the user I need to add. I can&#039t remove it as it&#039s a table owner, but I need to add it to the list of users, so I need to remove it then re-add it.<BR><BR>I tried using EXEC sp_changeobjectowner to no avail.<BR><BR>Thanks for any help/guidance.

  2. #2
    Dathi Guest

    Default RE: Restoring a database and losing users

    Hi,<BR><BR>Try using this script to add a login to go with the userid.<BR><BR>The script is written for SQL7<BR><BR>Replace the USERNAME with the login name you want(usually the same as the userid). Replace DATABASENAME with the name of your database.<BR><BR>You will then have a login to go with your username and the system should allow you to use it.<BR><BR>/*SQL codes starts here*/<BR>if not exists (select * from master..syslogins where name = &#039USERNAME&#039)<BR>BEGIN<BR> declare @logindb varchar(30), @loginlang varchar(30) select @logindb = &#039DATABASENAME&#039, @loginlang = null<BR> if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)<BR> select @logindb = &#039master&#039<BR> if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang &#060;&#062; &#039us_english&#039)<BR> select @loginlang = @@language<BR> exec sp_addlogin &#039USERNAME&#039, null, @logindb, @loginlang<BR>END<BR>GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts