How to Fix Orphaned Users in SQL Server Database
Aug 24, 2012
In this article we will take a look at the steps a database administrator needs to follow in order to fix orphaned users within a SQL Server Database.
When will you see Orphaned Users within a SQL Server Database?
You may end up seeing orphaned uses within a SQL Server Database when you have restored a database from one server to another or you want to attach a database which was detached from another SQL Server Instance.
For example for business reasons you may have to restoring a copy of your Production database on to a QA or a Development Server. In such scenarios your application login would be already present on the QA and Development Server. However, the SIDs for the application login would be different on each server for the same login resulting in Orphaned User.
Identifying Orphaned Users in SQL Server Database
Fixing Orphaned Users in a Database when Database Login and SQL Server Login is already available in SQL Server
How to Link the Specified User in a Current Database to an existing SQL Server Login
/* Create a New SQL Login */
CREATE LOGIN NewSQLLogin WITH PASSWORD = 'ERT#$%dfg#01'
/* Map Existing Database User MyTechMantra to Login NewSQLLogin */
EXEC sp_change_users_login 'Update_One', 'MyTechMantra', 'NewSQLLogin'
Reference:- sp_change_users_login (Transact-SQL)
In this article you have seen how to fix Orphaned Users in SQL Server Database.
Continue Free Learning...
Geeks who read this article also read…
- How to Attach Database Without a Transaction Log File in SQL Server
- How to Detect Virtual Log Files in SQL Server Transaction Log File
- How to Fix Virtual Log Files in SQL Server Transactional Log File
- How to Change SQL Server Database Auto Growth Settings
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…