0

This question already has an answer here:

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''

marked as duplicate by mustaccio, kevinsky, John Eisbrener, Vérace, Max Vernon sql-server Apr 16 at 19:14

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What exactly is wrong with the answer in the post you've linked? You've said you'd prefer not to use it - what exactly doesn't it do that you need it to? – George.Palacios Apr 16 at 9:38
1

A very elaborate way around your problem if you have to use sp_change_users_login

By using a cursor and inserting into the temp table inside the stored proc

use master;
GO

ALTER PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000) ;
DECLARE @dbname nvarchar(500);

CREATE TABLE #temptable (
    ID INT IDENTITY(1,1),
    [Dbname] nvarchar(500),
    [user] nvarchar(1000),
    [User_sid] sql_variant 
);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR select name from sys.databases where database_id > 4 and state_desc = 'ONLINE';
OPEN C
FETCH NEXT FROM C INTO @dbname;
WHILE @@FETCH_STATUS =0
    BEGIN
    SET @command = '
    use '+QUOTENAME(@dbname)+'

    EXEC sp_change_users_login ''Report'' '
    INSERT INTO #temptable([user],[User_sid])
    EXEC(@Command);
    IF @@ROWCOUNT >0
    BEGIN
    ;WITH CTE  AS
    (
        SELECT TOP(1) * FROM #temptable
        ORDER BY ID DESC
        )
        UPDATE CTE
        SET [Dbname] = @dbname;
    END
    FETCH NEXT FROM C INTO @dbname;
    END
SELECT * FROM #temptable;
END

I would advise against placing procedures / data in the master datatabase if you don't need to.

Running the proc

EXEC orphaned_u

Returns my two orphaned users

ID  Dbname       user   User_sid
1   my_test      test   0x4B0387CBECE23440B207E17B9F01160F
2   my_test_old  test2  0xF6F3F720927731449B6A9409F1248743

Not the answer you're looking for? Browse other questions tagged or ask your own question.