I need to get a list of the orphaned users in each DB, together with the database name in which they are found in. I did the below which gives the orphaned users in each DB, but I cannot figure out how to include another column next to each user found to show in which the DB. I tried using the DB_NAME() but to no avail.
use master;
GO
CREATE PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000)
SELECT @command = 'use ?
if DB_ID("?")>4
EXEC sp_change_users_login ''Report'' '
EXEC sp_MSforeachdb @command
END
CREATE TABLE tempTable (
user nvarchar(1000),
User_sid sql_variant
)
INSERT INTO tempTable
EXEC master..orphaned_u
SELECT * FROM dbo.tempTable
I have already looked at Find orphaned users but preferable I use the above code and find a way to display the database name in a column with the already built-in stored procedure sp_change_users_login ''Report''