This question already has an answer here:
- Find orphaned users 3 answers
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''