Problems with SQL Server backup and recovery

yry777

Ask a question

I backed up a database under SQL Server, and then restored the database to the same version on another machine. The user is user1. When using SQL statements, the following error always appears:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'XXX'

That is, select * from XXX will always be wrong, and replaced with
select * from user1.XXX is fine.

I found some information from the Internet. It may be a problem with the orphan account. I use
EXEC sp_change_users_login 'Report'
EXEC SP_CHANGE_USERS_LOGIN UPDATE_ONE, 'user1', 'user1' resolved the orphan account, but still reported the same error.

How do you recover on another SQL Server machine?

我是杀人狂

xxx is not owned by the current user, you can set permissions to allow access

yry777

The problem is that there are many tables, hundreds of them. Is there a SQL for batch setting?

yry777

Sorry, this permission has already been set. Are there any other points of attention?

jccg1124

I used sa for backup and recovery
If you have permission, change the currently restored table owner of the table to the corresponding username
Use cursors for more tables
sp_changeobjectowner [@objname =] 'object', [@newowner =] 'owner'

yry777

I describe my operation process:
I backed up with user1 under the original database, then created a user user1 under the sql server of another machine, and logged in with user1 to restore the database.

The owner of the restored database is user1. Then take
EXEC sp_change_users_login 'Report'
EXEC SP_CHANGE_USERS_LOGIN UPDATE_ONE, 'user1', 'user1' solve the problem of orphan accounts.

Also check that user1 is in the permission of each table. As usual, user1 should be able to access these tables. But SQL must be selected * from user1.xxx to use. Too puzzled.

yry777

Sorry, correct me, user1 is actually login, not user. I now find that this user under database has no login name. I wonder if this is the reason? So how do you associate this user1 to the same login?

yry777

Sorry, correct me, user1 is actually login, not user. I now find that this user under database has no login name. I wonder if this is the reason? So how do you associate this user1 to the same login?

I use EXEC SP_CHANGE_USERS_LOGIN UPDATE_ONE, 'user1', 'user1' just to associate the user with the database access attribute of login, but I don't know how to change the opposite direction

我是杀人狂

sp_changobjectowner to modify the owner of the data table, modify it to all the DBO

mapleshill

--Grant database access to'user1'
--Add'user1' to the role'db_owner'

USE DBNAME
IF USER_ID('user1') IS NULL
BEGIN
EXEC sp_grantdbaccess'user1','user1'
EXEC sp_addrolemember'db_owner','user1'
END
GO