Login/User problems after restore

:confused:

I have a question regarding SQLSERVER. I have 2 systems, production and development. I have restored a copy of the production database into the develeopment server to do some testing but am now having problems connecting to the database.

The issue
======

The database tables in the restored database are owned by a user HEATPROD in production and are stiil owned by that user after the restore. The USERS tab in SQL Server Enterprise Manager for this database does not show the user HEATPROD although the TABLES tab shows that user as owning the tables.

I have created the LOGIN of HEATPROD but am not able to grant this login access to the restored database as I get the error "ERROR 15023: USER OR ROLE HEATPROD ALREADY EXISTS IN THE CURRENT DATABASE".

An attemp to login as the HEATPROD user and access the new database generates the following error "SERVER USER HEATPROD IS NOT A VALID USER IN DATABASE DBATESTDB" however attempts to add this user to the database generate the 15023 error above.

Any suggestion on how to link the LOGIN to the USER and thus gain access to the database would be much appreciated.

TIAIt sounds as if you have an "orphaned user" in your database called HEATPROD.

To check this run the following

Use <Insert DB Name>
go
Select suid, name from sysusers
where name = 'HEATPROD'

If it does exist you need to delete it from this table.
To do that run the following ....

Use <Insert DB Name>
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
delete from sysusers where name = 'HEATPROD'
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go

When you have deleted the user, Refresh your database through Enterprise Manager.

You can now add the user to your database .... providing a login exists of course.

Hope this helps

P.C. Vaidyanathan|||The problem is that in the master database there is no user with that name.
This can happen when you restore a dabase to another server or domain. Look at microsoft for the solution Q218172|||Running

EXEC sp_change_users_login 'Auto_Fix', 'HEATPROD'

Should do the trick if you are running sql server authentication

Rosko|||Thanks,

It worked as suggested.:)|||This is a script I use to correct logins after restoring from one server to another. The results can be copied to the active pane (where you can run the script) to fix all orphaned users for the database in question.

select 'sp_change_users_login @.Action = ' + char(39) + 'auto_fix' +
char(39) + ', @.usernamepattern = ' + char(39) + name + char(39) + char(13) + 'go'
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name

Hope this helps.

Rory

Login problems after Restore

I restored a DB from one SQL server to the other. Before doing the Restore I created a blank DB on the destination server. The destination server already had all the logins that were present on the source server. I also created the same users in the destination DB as the source. Before doing the Restore if I look at the users in the Enterprise manager I can see all the users that I created. However after the Restore the only user I see in the Enterprise manager is 'dbo'! If I login using isql and go the newly restored DB and issue 'sp_helpuser', it shows me all the users that I created.

So why don't I see these users from Enterprise manager?? It looks like an ID mismatch problem between syslogins and sysusers.

Next I login using ISQL to the destination server using one of the corrupted? logins. I try to go to the restored DB and it complains that this is not a valid user. So I come out and login in again as SA and go the restored database and try to add the earlier user. It says user already exists. :mad: I'm not sure whats the right way out here. :confused: Any help appreciated.

ThanksIssue sp_change_users_login 'report' which will display all unmapped SQL Server Standard Security-based users that need to be fixed. You can either issue sp_change_users_login 'Update_One', <userid>, <login>, or generate a script with the above syntax.

login problem after restoring a database in SQL7

Since the database server was removed to a new Windows Advanced Server 2000, I restored a database from the backup file. When I tried to create a new Login under Security, I got the following error message:

Error 15023: User or role 'MyDatabaseLoginName' already exists in current database.

But I checked it and it doesn't exist. I even could not create a system DSN without the Login. Someone told me to create a new one with another name, but I do not want to change the connection string in my ASP files. Does anyone has any idea about it? Thanks in advance.Did you try to fix it with this system stored proc, can be used in sql server login, should fit ur situation.

sp_change_users_login

Login problem after DB restore

SQL 7.0 using SQL/Windows authenication.
A DB backup was restored from one server to another which
had a new install of SQL 7.0.
Problem:
In EM/Security, if I try to add the user login 'xxx' with
access to the restored DB, SQL says the login name
already exists in the the DB. Yet, it doesn't exist, that
I can see any where. If I try to access the DB from an
ODBC connection using the login 'xxx' it denies access.
I tried droplogin 'xxx', but that didn't help.
Any ideas?
Thanks,
DonRead up on sp_change_users_login within Books Online. I am sure that it =
will solve your problem.
--=20
Keith
"Don" <anonymous@.discussions.microsoft.com> wrote in message =
news:763a01c406aa$374e45a0$a601280a@.phx.gbl...
> SQL 7.0 using SQL/Windows authenication.
>=20
> A DB backup was restored from one server to another which=20
> had a new install of SQL 7.0.=20
>=20
> Problem:
> In EM/Security, if I try to add the user login 'xxx' with=20
> access to the restored DB, SQL says the login name=20
> already exists in the the DB. Yet, it doesn't exist, that=20
> I can see any where. If I try to access the DB from an=20
> ODBC connection using the login 'xxx' it denies access.
> I tried droplogin 'xxx', but that didn't help.
> Any ideas?
> Thanks,
> Don
>

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:526E5BAE50074@.SQLMonster.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200508/1
HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian
|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via droptable.com" <forum@.droptable.com> wrote in message
news:526E5BAE50074@.droptable.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200508/1HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via droptable.com" <forum@.droptable.com> wrote in message
news:526E5BAE50074@.droptable.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1