Using a Backup to Find SQL Server Security Changes

USE Test;
GO 
CREATE USER [Johnny] WITHOUT LOGIN;
GO 
CREATE USER [Mary] WITHOUT LOGIN;
GO 
CREATE TABLE dbo.ATable (TableValue CHAR(10));
GO 
GRANT SELECT ON OBJECT::dbo.ATable TO [Johnny];
DENY SELECT ON OBJECT::dbo.ATable TO [Mary];
GO 

USE Test;
GO 
REVOKE SELECT ON OBJECT::dbo.ATable TO [Mary];
GO 
GRANT INSERT, UPDATE, DELETE ON OBJECT::dbo.ATable TO [Johnny];
GO 

-- See what all the old permissions were
-- This is at the object (not schema or DB) level.

SELECT perm.state_desc, perm.permission_name, o.[name] AS 'Object', prin.[name]
FROM Test_Old.sys.database_permissions perm
  JOIN Test_Old.sys.database_principals prin
    ON perm.grantee_principal_id = prin.principal_id
  JOIN Test_Old.sys.objects o
    ON perm.major_id = o.object_id
WHERE perm.class = 1; -- This keeps it at the object level

-- See what the old permissions were for a 
-- single user. This is at the object level.

SELECT perm.state_desc, perm.permission_name, o.[name], prin.[name]
FROM Test_Old.sys.database_permissions perm
  JOIN Test_Old.sys.database_principals prin
    ON perm.grantee_principal_id = prin.principal_id
  JOIN Test_Old.sys.objects o
    ON perm.major_id = o.object_id
WHERE prin.[name] = 'Johnny'
  AND perm.class = 1; -- This keeps it at the object level

-- See what has changed. Note the union all and the use of LEFT/RIGHT JOIN.
-- This will keep a single set of columns without nulls.

SELECT 'Removed' AS 'Action', OldDB.state_desc, OldDB.permission_name, 
    OldDB.[object], OldDB.[name]
FROM 
 (SELECT perm.grantee_principal_id, perm.class, perm.major_id, perm.[type], perm.[state],
    perm.state_desc, perm.permission_name, o.[name] AS 'Object', prin.[name]
  FROM Test_Old.sys.database_permissions perm
    JOIN Test_Old.sys.database_principals prin
      ON perm.grantee_principal_id = prin.principal_id
    JOIN Test_Old.sys.objects o
      ON perm.major_id = o.object_id
  WHERE perm.class = 1) AS OldDB
LEFT JOIN
  (SELECT perm.grantee_principal_id, perm.class, perm.major_id, perm.[type], perm.[state],
     perm.state_desc, perm.permission_name, o.[name] AS 'Object', prin.[name]
   FROM Test.sys.database_permissions perm
     JOIN Test.sys.database_principals prin
       ON perm.grantee_principal_id = prin.principal_id
     JOIN Test.sys.objects o
       ON perm.major_id = o.object_id
   WHERE perm.class = 1) AS NewDB
ON OldDB.grantee_principal_id = NewDB.grantee_principal_id 
  AND OldDB.class = NewDB.class
  AND OldDB.major_id = NewDB.major_id
  AND OldDB.[type] = NewDB.[type]
  AND OldDB.[state] = NewDB.[state]
WHERE NewDB.major_id IS NULL
UNION ALL
SELECT 'Added' AS 'Action', NewDB.state_desc, NewDB.permission_name, 
    NewDB.[object], NewDB.[name]
FROM 
  (SELECT perm.grantee_principal_id, perm.class, perm.major_id, perm.[type], perm.[state],
     perm.state_desc, perm.permission_name, o.[name] AS 'Object', prin.[name]
   FROM Test_Old.sys.database_permissions perm
     JOIN Test_Old.sys.database_principals prin
       ON perm.grantee_principal_id = prin.principal_id
     JOIN Test_Old.sys.objects o
       ON perm.major_id = o.object_id
   WHERE perm.class = 1) AS OldDB
RIGHT JOIN -- Note Change from LEFT JOIN
  (SELECT perm.grantee_principal_id, perm.class, perm.major_id, perm.[type], perm.[state],
     perm.state_desc, perm.permission_name, o.[name] AS 'Object', prin.[name]
   FROM Test.sys.database_permissions perm
     JOIN Test.sys.database_principals prin
       ON perm.grantee_principal_id = prin.principal_id
     JOIN Test.sys.objects o
       ON perm.major_id = o.object_id
   WHERE perm.class = 1) AS NewDB
ON OldDB.grantee_principal_id = NewDB.grantee_principal_id 
  AND OldDB.class = NewDB.class
  AND OldDB.major_id = NewDB.major_id
  AND OldDB.[type] = NewDB.[type]
  AND OldDB.[state] = NewDB.[state]
WHERE OldDB.major_id IS NULL;