Orphaned users in SQL Server

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’

Links …

Different methods of moving data between 2 tables

1.    Insert into db.schema.table select * from db.schema.tab where condition

2.    Insert into db.schema.tab (col1,col2,col2,…) select col1,col2,col3,… from db.schema.tab where condition

3.    Bcp db.schema.tab OUT filepath –n –s(servername/instancename) –t –e(error file path).

4.    Bcp db.schema.tab IN filepath –n –s(servername/instancename) –t –e(error file path).

5.    We can use DTS/SSIS/BULK INSERT commands.

6.    BULK INSERT Table_Name

FROM 'C:\Program Files\File.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

7.    Create a new table (duplicate structure), located in the new filegroup.

Copy the current table's data to the new table.

Remove any PK-FK relationships.

DROP the old table

RENAME the new table to the old table name

Re-create the PK-FK relationships.

(OR)

Drop any existing CLUSTERED index.

Re-Create the CLUSTERED index on the new filegroup

-n uses the native (database) data types

-S should be added before server name

-T using a trusted connection

-e should be added before filepath for error file that logs the failed rows