--Create login(Orphan)
USE [master]
GO
CREATE LOGIN [Orphan] WITH PASSWORD=N'Sql@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--Drop login(orphan)
USE [master]
GO
DROP LOGIN [Orphan]
GO
--SID of a login
SELECT
name AS LoginName,
sid AS LoginSID
FROM sys.server_principals
WHERE name = 'orphan';
--SID of a user
SELECT
dp.name AS UserName,
dp.sid AS UserSID
FROM sys.database_principals dp
WHERE dp.name = 'orphan';
--map the login to user
USE Demo;
GO
ALTER USER orphan WITH LOGIN = orphan;
--Create login with same user id
CREATE LOGIN orphan
WITH PASSWORD = 'Sql@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,
SID = 0x9EF75C0E884EC84E8467FCE6FC8E0F2C;
--check orphan users
exec sp_change_users_login 'report'
Download
0 formats
No download links available.
How to fix orphan user with different SID in SQL Server | NatokHD
