SQL Server 2016 Hosting – HostForLIFE.eu :: How to Fix and List Out Orphan Users on All the Databases on Server?

Peter Cole | October 25, 2017 | SQL 2016 Hosting | No Comments

In this short article, we are going to fix and list out Orphan Users on all the databases on server. First open you SQL Server and then write the following code:

USE MASTER
GO
SET NoCount ON
DECLARE @VarDbId INT,
@SQL nvarchar(4000),
@VDBName nvarchar(260),
@OUCounter INT,
@Max_OUCounter INT
SELECT @VarDbId=4,
@SQL =''
CREATE TABLE #OrphaneUsers
(
ID INT IDENTITY (1,1) NOT NULL,
DBName VARCHAR(125) NULL ,
UserName sysname NULL ,
UserSID VARBINARY(85) NULL ,
LoginExists bit NULL
)
WHILE EXISTS
(SELECT database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
)
BEGIN
SELECT TOP 1
@SQL ='Create table #OrphaneUser
(UserName sysname null,
UserSID varbinary(85) null )
insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''
insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser
drop Table #OrphaneUser',
@VDBName=name
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
ORDER BY database_id
EXEC SP_Executesql @SQL
SELECT TOP 1
@VarDbId=database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
END
UPDATE #OrphaneUsers
SET LoginExists=1
FROM #OrphaneUsers
JOIN syslogins
ON #OrphaneUsers.UserName=syslogins.NAME
SELECT @OUCounter =0,
@Max_OUCounter =COUNT(0)
FROM #OrphaneUsers
WHERE LoginExists=1
WHILE EXISTS
(SELECT TOP 1
id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
)
BEGIN
SELECT TOP 1
@OUCounter=id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Auto_Fix'', '''+UserName+''', NULL, '''+UserName+''''
FROM #OrphaneUsers
WHERE LoginExists=1
AND id =@OUCounter
EXEC SP_Executesql @SQL
PRINT @SQL
END
SELECT *
FROM #OrphaneUsers
DROP TABLE #OrphaneUsers

USE MASTER

SET NoCount ON

DECLARE @VarDbId INT,

@SQL nvarchar(4000),

@VDBName nvarchar(260),

@OUCounter INT,

@Max_OUCounter INT

SELECT @VarDbId=4,

@SQL =''

CREATE TABLE #OrphaneUsers

(

ID INT IDENTITY (1,1) NOT NULL,

DBName VARCHAR(125) NULL ,

UserName sysname NULL ,

UserSID VARBINARY(85) NULL ,

LoginExists bit NULL

)

WHILE EXISTS

(SELECT database_id

FROM sys.databases

WHERE database_id>@VarDbId

AND state_desc ='ONLINE'

)

BEGIN

SELECT TOP 1

@SQL ='Create table #OrphaneUser

(UserName sysname null,

UserSID varbinary(85) null )

insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''

insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser

drop Table #OrphaneUser',

@VDBName=name

FROM sys.databases

WHERE database_id>@VarDbId

AND state_desc ='ONLINE'

ORDER BY database_id

EXEC SP_Executesql @SQL

SELECT TOP 1

@VarDbId=database_id

FROM sys.databases

WHERE database_id>@VarDbId

AND state_desc ='ONLINE'

END

UPDATE #OrphaneUsers

SET LoginExists=1

FROM #OrphaneUsers

JOIN syslogins

ON #OrphaneUsers.UserName=syslogins.NAME

SELECT @OUCounter =0,

@Max_OUCounter =COUNT(0)

FROM #OrphaneUsers

WHERE LoginExists=1

WHILE EXISTS

(SELECT TOP 1

FROM #OrphaneUsers

WHERE LoginExists=1

AND id >@OUCounter

)

BEGIN

SELECT TOP 1

@OUCounter=id

FROM #OrphaneUsers

WHERE LoginExists=1

AND id >@OUCounter

SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Auto_Fix'', '''+UserName+''', NULL, '''+UserName+''''

FROM #OrphaneUsers

WHERE LoginExists=1

AND id =@OUCounter

EXEC SP_Executesql @SQL

PRINT @SQL

END

SELECT *

FROM #OrphaneUsers

DROP TABLE #OrphaneUsers

I hope it works for you! Thank you.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Related Posts

About The Author

Pete