When you restore a database backup to another server, you may experience a problem with orphaned users.#

  • Add a login to the master database, and specify the default database as Northwind: Use master go
sp_addlogin 'test', 'password', 'Northwind'
  • Grant access to the user you just created:
Use Northwind go sp_grantdbaccess 'test'
  • Backup the database.
BACKUP DATABASE Northwind
TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
  • Restore the database to a different SQL Server server:
RESTORE DATABASE Northwind
FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
  • The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned. Now, to detect orphaned users, run this code:
Use Northwind go sp_change_users_login 'report'

Steps To Resolve Orphaned Users#

  • Run the following command for the orphaned user from the preceding step:
Use Northwind
go
sp_change_users_login 'update_one', 'test', 'test'
This relinks the server login "test" with the the Northwind 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.
  • After you run the code in the preceding step, the user can access the database. The user may then alter the password with the sp_password stored procedure:
Use master 
go
sp_password NULL, 'ok', 'test'
Back to SQL Server

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-1) was last changed on 24-May-2017 15:30 by UnknownAuthor
G’day (anonymous guest) My Prefs
  • View Page Source
  • This clear IPSec security association,
    clear ipsec sa peer X.X.X.X
    

All Pages

Page views: 1820

Private Tomcat

Linux

MySQL

Email

SQL Server

ASP

JSP

C#

Web Mail

Windows Plesk

Linux Plesk

PHP

Gaming

ASP.NET

Persits ASPUpload

Wiki Help

Referring Pages:
...nobody

JSPWiki v2.8.1