Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This blog about the practical solutions for the problems that SQL server DBA face in their day to day work
Browse by Tags · View All
Internals 12
Index 12
Implimentation 6
Administration 6
Security 6
Failover cluster 6
Troubleshooting 5
Index Fragmentation 3
SSRS 2
Alerts 2

Archive · View All
September 2012 10
March 2013 9
August 2012 9
March 2012 9
July 2012 8
October 2012 5
June 2012 4
April 2012 4
April 2013 3
February 2012 2

PracticalSqlDba

SQL Server : Fixing the Orphaned Users

Aug 3 2012 2:10AM by Nelson John A   


In my last post , we have discussed about orphaned uses and how to find out the orphaned users. In this post let us see how to fix the orphaned users.

In the scenario where the login is got deleted , the orphan users can be fixed by dropping the user from the databases using the below script.
DROP USER Testwindows

Some time the above script may throw an error saying that the users owns a schema.Find out the schema owned by this login by querying the catalog view sys.schema and mapping the principal_id to sys.database_principals.Either drop these schemas or change the ownership based on your environment. To change the ownership of the schema ,use the below command

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO NewUserName;

In the scenario where  database restored in a different environment, we can fix the SID mismatch between the sys.server_principals and sys.database_principals by using the system stored procedure sp_change_users_login. For example you have login Mydomain\Lastname.Firstname in two instances namely INST1 and INST2. This login is associated with the user TestWindowsUser with  db_owner right on one of the database (MyDb) in INST1. Now you have taken backup of MyDb and restored it on the INST2 and  Mydomain\Lastname.Firstname will not be able to access the restored database as its SID is not matching with sys.server_principals SID . To fix this issue we can run the below command on the restored database.
USE MyDb
GO
Exec sp_change_users_login 'update_one', 'TestWindowsUser', 'Mydomain\Lastname.Firstname'

If you liked this post, do like my page on FaceBook 


Republished from Practical SQL DBA [24 clicks].  Read the original version here [3 clicks].
Nelson John A
478 · 0% · 78
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect


Submit

Your Comment
Sign Up or Login to post a comment.
"SQL Server : Fixing the Orphaned Users" rated 5 out of 5 by 1 readers
SQL Server : Fixing the Orphaned Users , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]