After Restore ,Problem.Help me

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

After Restore ,Problem.Help me

Author

Topic

radha
Starting Member

11 Posts

Posted - 2004-10-08 : 05:17:32

I have Problem after restoring a Database.
Actually our Database username : Supportdbo
and Passoword : Supportdbo
and Owner of Datbase Objects : Supportdbo
This Owner does not have sa role.
I have made a Database-Complete Backup using Enterprise Manager.
I created new Database 'TEST' .Before Restoring the Database I have created user and gave Permission for that Datatbase 'TEST'
I restored using Enterprise Manager. its restored Properly.After that I could't able to login in Query Analyser using that User Supportdbo.Please help me to resolve this Problem.

fvinagre
Starting Member

29 Posts

Posted - 2004-10-08 : 05:30:52

When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.

Use master
sp_addlogin 'test', 'password', 'Northwind'

SELECT sid FROM dbo.sysxlogins WHERE name = 'test'
0xE5EFF2DB1688C246855B013148882E75

Grant access to the user you just created

Use Northwind
sp_grantdbaccess 'test'

SELECT sid FROM dbo.sysusers WHERE name = 'test'
0xE5EFF2DB1688C246855B013148882E75

As you can see, both SID's are identical.

Backup the database

Use master
BACKUP DATABASE Northwind
TO DISK = 'C:\Northwind.bak'

Copy the Backupfile to another Maschine and SQL Server and restore it as follows:

RESTORE FILELISTONLY
FROM DISK = 'C:\Users\Zahn\Work\Northwind.bak'
Northwind
Northwind_log

RESTORE DATABASE TestDB
FROM DISK = 'C:\Users\Zahn\Work\Northwind.bak'
WITH
MOVE 'Northwind' TO 'D:\DataMSSQL\Data\northwnd.mdf',
MOVE 'Northwind_log' TO 'D:\DataMSSQL\Data\northwnd.ldf'

The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.

Check the SID's

Use master
SELECT sid FROM dbo.sysxlogins WHERE name = 'test'
0x39EE98D37EAC2243B7833705EC1C60E3

Use TestDB
SELECT sid FROM dbo.sysusers WHERE name = 'test'
0xE5EFF2DB1688C246855B013148882E75

Now, to detect orphaned users, run this code

Use TestDB
sp_change_users_login 'report'
test 0xE5EFF2DB1688C246855B013148882E75

The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the TestDB database, and the sysxlogins system table in the master database.

Resolve Orphaned Users

Use TestDB
sp_change_users_login 'update_one', 'test', 'test'

SELECT sid FROM dbo.sysusers WHERE name = 'test'
0x39EE98D37EAC2243B7833705EC1C60E3

use master
SELECT sid FROM dbo.sysxlogins WHERE name = 'test'
0x39EE98D37EAC2243B7833705EC1C60E3

This relinks the server login "test" with the the TestDB database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.

radha
Starting Member

11 Posts

Posted - 2004-10-08 : 22:40:10

Thank you very much.It's working.Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources