To detect orphaned users, execute the following Transact-SQL statements per database:

USE <database_name>;GO;sp_change_users_login @Action=’Report’;GO;

Possible Response

The output lists the users and corresponding security identifiers (SID) in the current database that are not linked to any SQL Server login. For more information, see sp_change_users_login (Transact-SQL).

To repare the broken relation between sql user and db user you have to create the sql user . After this use the following procedure to recreate the relationship.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO
 

Example:

sp_change_users_login @Action=’update_one’, @UserNamePattern=’T-PrintAccounting’, @LoginName=’ T-PrintAccounting”;

You need possible a change of the user password. To do this use

USE master
GO
sp_password @old=NULL, @new='password', @loginame='<login_name>';
GO
 

Open SQL Management Studio.
Disconnect all server connections.
Leave GUI open as we need the Query Analizer

stop all SQL services

open services by running service.msc

open properties of the sql instance and copy the sql executing path
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL1\MSSQL\Binn\sqlservr.exe -c -f -sSQL1

run this command line with additional option –c –f
now is a sql instance in single user mode running
Leave the process running, we come back later on

change to SQL Management Studio and run a new query as sa user
execute the following sql code after adjusting it to your needs

Use Master
go
 
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', NEWNAME = 'tempdev1', FILENAME = N'T:\SQL1\tempDB\tempdev1.ndf')
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev1', SIZE = 5120000KB , FILEGROWTH = 51200KB )
go
 
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', SIZE = 2048KB , FILEGROWTH = 1024KB);
GO
 
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'T:\SQL1\tempDB\tempdev2.ndf' , SIZE = 5120000KB , FILEGROWTH = 51200KB )
GO
 
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'T:\SQL1\tempDB\tempdev3.ndf' , SIZE = 5120000KB , FILEGROWTH = 51200KB )
GO
 
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'T:\SQL1\tempDB\tempdev4.ndf' , SIZE = 5120000KB , FILEGROWTH = 51200KB )
GO
 
Use Master
go
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = N'T:\SQL1\tempDBlog\templog.ldf')
GO

close the query window
Then got to the command line and press ctr+c to terminate the single user session
now remove all temp db files . they will be created on next sql service start.
next start all sql services and check the new settings