Orphaned Users in SQL Server

In SQL Server, an orphaned user is a database user that is not mapped to a valid SQL Server login at the instance level. It is a user that exists in a database (Database-Security-Users) but for any reason, does not have a corresponding login in the instance’s security (master database). So as a standard practice we have to fix the orphaned users by mapping it with login or removing orphaned user from any given database.

This usually happens when:

• A database is restored or attached from another SQL Server instance.

• A login was deleted at the server level, but the user still exists inside the database.

• The SID (security identifier) between the login and user doesn’t match.

1. Finding Orphaned users

--List out Orphan USERS for a given database
EXEC sp_change_users_login 'Report';
OR
select db_name() as DBName,* from sys.database_principals where sid not in (select sid from master.sys.server_principals) AND type_desc != 'DATABASE_ROLE' AND name != "guest";

--List out Orphan USERS from all the databases
exec sp_msforeachdb ' use [?] EXEC sp_change_users_login ''Report''';
OR
select db_name() as DBName,* from sys.database_principals where sid not in (select sid from master.sys.server_principals) AND type_desc != 'DATABASE_ROLE' AND name != "guest";

Note: sp_change_users_login will not show orphan users for windows\AD accounts.

2. Fix Orphaned users

--If you already have a Login_Name and Password for User_Name, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'User_Name';

--If you want to create a new Login_Name with password (Ex. P@ssword123) for User_Name fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'User_Name', 'Login_Name', 'P@ssword123';

--Fix all Orphaned users at once
USE DB_Name  -- change db name for which you want to fix orphan users issue
GO

declare @name varchar(150)
DECLARE cur CURSOR FOR
select name from master..syslogins

Open cur
FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'AUTO_FIX', @name
FETCH NEXT FROM cur into @name
END

CLOSE cur
DEALLOCATE cur

3. Delete Orphaned users

--Drop orphaned user from Database.
use DB_Name
go
DROP user User_Name
go

--Drop all orphaned users from custom SP
use [master]
go
create proc dbo.sp_Drop_OrphanedUsers
as
begin
set nocount on

-- get orphaned users
declare @user varchar(max)
declare c_orphaned_user cursor for
select name
from sys.database_principals
where type in ('G','S','U')
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') )
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser') open c_orphaned_user
fetch next from c_orphaned_user into @user
while(@@FETCH_STATUS=0)
begin

-- alter schemas for user
declare @schema_name varchar(max)
declare c_schema cursor for
select name from sys.schemas where USER_NAME(principal_id)=@user
open c_schema
fetch next from c_schema into @schema_name
while (@@FETCH_STATUS=0)
begin
declare @sql_schema varchar(max)
select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'
print @sql_schema
exec(@sql_schema)
fetch next from c_schema into @schema_name
end
close c_schema
deallocate c_schema

-- alter roles for user
declare @dp_name varchar(max)
declare c_database_principal cursor for
select name from sys.database_principals
where type='R' and user_name(owning_principal_id)=@user
open c_database_principal
fetch next from c_database_principal into @dp_name
while (@@FETCH_STATUS=0)
begin
declare @sql_database_principal varchar(max)
select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'
print @sql_database_principal
exec(@sql_database_principal )
fetch next from c_database_principal into @dp_name
end
close c_database_principal
deallocate c_database_principal

-- drop roles for user
declare @role_name varchar(max)
declare c_role cursor for
select dp.name--,USER_NAME(member_principal_id)
from sys.database_role_members drm
inner join sys.database_principals dp
on dp.principal_id= drm.role_principal_id
where USER_NAME(member_principal_id)=@user
open c_role
fetch next from c_role into @role_name
while (@@FETCH_STATUS=0)
begin
declare @sql_role varchar(max)
select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''
print @sql_role
exec (@sql_role)
fetch next from c_role into @role_name
end
close c_role
deallocate c_role

-- drop user
declare @sql_user varchar(max)
set @sql_user='DROP USER ['+@user +']'
print @sql_user
exec (@sql_user)
fetch next from c_orphaned_user into @user
end
close c_orphaned_user
deallocate c_orphaned_user
set nocount off
end
go

-- Mark stored procedure as a system stored procedure
exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers
go

--To delete orphaned users from given DB (DB_Name)
USE [DB_Name]
GO
EXEC sp_Drop_OrphanedUsers;
go

--To delete orphaned users from all databases
USE [master]
GO
EXEC sp_msforeachdb 'USE [?]; EXEC sp_Drop_OrphanedUsers'
go

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.TechWithJC.com.