verbesserte Version:

DECLARE @LoginName NVARCHAR(128) = N'jira-sync-artikel'; -- ← Loginname hier anpassen
DECLARE @LoginSID VARBINARY(85);
DECLARE @sql NVARCHAR(MAX) = N'';

-- Login-SID holen
SELECT @LoginSID = sid
FROM sys.server_principals
WHERE name = @LoginName;

IF @LoginSID IS NULL
BEGIN
    PRINT 'Login nicht gefunden.';
    RETURN;
END

-- Temp-Tabelle vorbereiten
IF OBJECT_ID('tempdb..#LoginMappings') IS NOT NULL DROP TABLE #LoginMappings;

CREATE TABLE #LoginMappings (
    DatabaseName SYSNAME,
    DatabaseUserName SYSNAME,
    UserType NVARCHAR(60),
    CreateDate DATETIME,
    ModifyDate DATETIME,
    RoleName SYSNAME NULL -- ← Hier erlaubt: NULL-Werte für User ohne Rollen
);


-- Dynamisches SQL bauen
SELECT @sql += '
USE [' + name + '];

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE sid = 0x' + CONVERT(VARCHAR(MAX), @LoginSID, 2) + ')
BEGIN
    INSERT INTO #LoginMappings (DatabaseName, DatabaseUserName, UserType, CreateDate, ModifyDate, RoleName)
    SELECT
        ''' + name + ''' AS DatabaseName,
        dp.name,
        dp.type_desc,
        dp.create_date,
        dp.modify_date,
        dr.name AS RoleName
    FROM sys.database_principals dp
    LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
    LEFT JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
    WHERE dp.sid = 0x' + CONVERT(VARCHAR(MAX), @LoginSID, 2) + '
END
'
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND name NOT IN ('tempdb');

-- Ausführen
EXEC sp_executesql @sql;

-- Ausgabe
SELECT * FROM #LoginMappings
ORDER BY DatabaseName, DatabaseUserName, RoleName;

-- Aufräumen
DROP TABLE #LoginMappings;