When a database is moved from server A to server B the users will not be able to perform any actions this is due to login and user conflict. Such users that are having login conflict are termed as Orphan users.
Difference between login and users:
A login gives access to the server. So any login created will be able to connect to server but will not be able to access any user databases. In order to get access to database, a user should be created in database with mapping to the login. Once a user will be created with mapping to a login then it gives access to the database objects.
To explain in general terms, a society having different houses can be termed as a server with different databases and different rooms of each house are different database objects. So if any person to enter any room of a particular house, first he should be allowed to enter the society and then he should be allowed to enter a particular house and then the rooms.
Same way a user in SQL Server in order to access any database object should first get access to the server and then he should get access to individual databases and then to database objects.
In case of orphaned user, if user SCOTT is there already in the database AdventureWorks and the database is moved to another server but there is no corresponding Login at server level, then user SCOTT cannot access the database or server.
So in order to resolve this login should be created for the user SCOTT at server level and map it the user of AdventureWorks database.
To find Orphaned users:
exec sp_change_users_login 'report'
To fix orphaned users:
Exec sp_change_users_login ‘UPDATE_ONE’,’database_user’,’login_name’
Very nice.. Really very helpful
ReplyDelete