DECLARE @LoginName NVARCHAR(128) = N'Username'; -- ← 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
);
-- 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;
|