Preskočiť na obsah
Skript

Porovnanie DB rolí dvoch loginov naprieč databázami

Rozdiel členstva v databázových rolách medzi dvoma loginmi vo všetkých online používateľských databázach (podľa SID).

#login #migration #roles
porovnanie-db-roli-dvoch-loginov-napriec-databazami.sql 52 riadkov · T-SQL
Parametre skriptu doplň a kopíruj hotový skript
/* DeadlockCafe | Porovnanie DB rolí dvoch loginov naprieč databázami
   Účel:  Rozdiel členstva v databázových rolách medzi dvoma loginmi vo všetkých online používateľských databázach (podľa SID).
   Režim: READ ONLY | SQL Server 2016 – 2022, on-premises
*/
SET NOCOUNT ON;

DECLARE @LoginA sysname = N'{{LoginA}}';
DECLARE @LoginB sysname = N'{{LoginB}}';
DECLARE @SidA varbinary(85) = SUSER_SID(@LoginA);
DECLARE @SidB varbinary(85) = SUSER_SID(@LoginB);
IF @SidA IS NULL OR @SidB IS NULL
    THROW 51003, 'Jeden z loginov neexistuje; skontroluj @LoginA/@LoginB.', 1;

IF OBJECT_ID(N'tempdb..#roles') IS NOT NULL DROP TABLE #roles;
CREATE TABLE #roles (database_name sysname, login_name sysname, role_name sysname);
DECLARE @db sysname, @sql nvarchar(max);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
 SELECT name FROM sys.databases
 WHERE database_id > 4 AND state_desc='ONLINE' AND HAS_DBACCESS(name)=1
 ORDER BY name;
OPEN c; FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'USE '+QUOTENAME(@db)+N';
      INSERT INTO #roles (database_name,login_name,role_name)
      SELECT DB_NAME(),
             CASE WHEN m.sid=@A THEN @LoginA ELSE @LoginB END,
             r.name
      FROM sys.database_principals AS m
      JOIN sys.database_role_members AS rm ON rm.member_principal_id=m.principal_id
      JOIN sys.database_principals AS r ON r.principal_id=rm.role_principal_id
      WHERE m.sid IN (@A,@B);';
  BEGIN TRY
    EXEC sys.sp_executesql @sql, N'@A varbinary(85),@B varbinary(85),@LoginA sysname,@LoginB sysname',
         @A=@SidA,@B=@SidB,@LoginA=@LoginA,@LoginB=@LoginB;
  END TRY
  BEGIN CATCH
    PRINT CONCAT('SKIPPED ',@db,': ',ERROR_MESSAGE());
  END CATCH;
  FETCH NEXT FROM c INTO @db;
END;
CLOSE c; DEALLOCATE c;

SELECT COALESCE(a.database_name,b.database_name) AS database_name,
       COALESCE(a.role_name,b.role_name) AS role_name,
       CASE WHEN a.role_name IS NULL THEN 'ONLY_B'
            WHEN b.role_name IS NULL THEN 'ONLY_A' ELSE 'BOTH' END AS comparison
FROM (SELECT database_name,role_name FROM #roles WHERE login_name=@LoginA) AS a
FULL JOIN (SELECT database_name,role_name FROM #roles WHERE login_name=@LoginB) AS b
 ON a.database_name=b.database_name AND a.role_name=b.role_name
ORDER BY database_name,role_name;

## Kedy použiť

Pri konsolidácii servisných účtov, po migrácii aplikácie alebo keď „jeden účet funguje a druhý nie“.

## Ako čítať výsledok

`ONLY_A` = rola len pre prvý login, `ONLY_B` = len pre druhý, `BOTH` = spoločná. Porovnáva sa po databáze a role. Nedostupné databázy sú vypísané v Messages ako SKIPPED.

> [!WARNING]
> Nepokrýva explicitné GRANT / DENY, členstvo cez AD skupiny ani vlastníctvo objektov. Žiadne práva nemení.

**Oprávnenia:** Prístup (CONNECT) do preverovaných databáz a viditeľnosť ich principals.  
**Režim:** READ ONLY · **Verzia:** SQL Server 2016 – 2022

## Súvisiace recepty

- [Členstvo v databázových rolách](script.php?id=36&s=clenstvo-v-databazovych-rolach)
- [Explicitné GRANT / DENY v databáze](script.php?id=38&s=explicitne-grant-deny-v-databaze)