SQLSERVER, change Database Isolation Level. Change database Collation. sp_change_users_login

==============================
Changing Database Isolation Level
==============================
How to resolve deadlock on Database level when changing Database Isolation Level

USE Jiratempdb
SELECT CASE transaction_isolation_level 
  WHEN 0 THEN 'Unspecified' 
  WHEN 1 THEN 'ReadUncommitted' 
  WHEN 2 THEN 'ReadCommitted' 
  WHEN 3 THEN 'Repeatable' 
  WHEN 4 THEN 'Serializable' 
  WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
  FROM sys.dm_exec_sessions 
where session_id = @@SPID

result: ReadCommitted
USE Jiratempdb
SELECT is_read_committed_snapshot_on 
  FROM sys.databases 
 WHERE name = 'Jiratempdb'

result = 0

In case of a deadlock error:

use master
go

EXEC sp_who2
SELECT * from sys.dm_exec_requests 
 WHERE command like 'ATRER%' 
ORDER BY command

kill <pid>


SET DEADLOCK_PRIORITY HIGH
GO

ALTER DATABASE XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE XXX set READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE XXX SET MULTI_USER
GO

USE Jiratempdb
SELECT is_read_committed_snapshot_on 
  FROM sys.databases 
 WHERE name = 'Jiratempdb'

result = 1



==============================
Changing Collation
==============================

USE master;  
GO

--Verify the collation setting.
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'jiradbdev';  
GO  
jiradbdev; SQL_Latin1_General_CP1_CI_AS

ALTER DATABASE XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE XXX COLLATE SQL_Latin1_General_CP437_CI_AI;
GO  

ALTER DATABASE XXX SET MULTI_USER
GO

  
--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'jiradbdev';  
GO  
jiradbdev; SQL_Latin1_General_CP437_CI_AI

==============================
sp_change_users_login 'AUTO_FIX', 'my_user'
==============================
This command Maps database user to a SQL Server login.

It will be needed, if migrating database from InstanceA to InstanceB, via detach+attach, and using same user, which is a local user on InstanceA and on InstanceB.

Auto_Fix option -  Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. 

If a login with the same name does not exist, one will be created.