Using a Backup to Find SQL Server Security Changes

Problem

We've recently determined that security changes were made for a particular user in a database. We didn't have any traces or any other auditing going so we don't know what changed. We, do, however, have a recent backup from before when we believe the changes were made. How can I use this to determine what permissions were altered?  Check out this tip to learn more.

Solution

If you have a backup from before the security changes were made, you can use it to determine the following:

• What the old permissions were?
• What changes were made?

The caveat is either you'll have to restore the backup or use a third-party product that can attach the backup like a database. Let's set up a sample situation in a test database (I've created a sample one called Test):

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 

Here we see that Johnny can SELECT against ATable, but Mary cannot. Actually, Mary has an explicit DENY permission on the object. At this point, I'll take a DB back and restore it as Test_Old. With the backup taken, let's make some permission changes:

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

Now, our requisite queries. Note, these queries are for SQL Server 2005 and above. You could do something similar with sysprotects in SQL Server 2000. Also, in this case I'm centering on object permissions. If you're using permissions at schema and database levels, you probably want to compare them, too.

Seeing what all the old SQL Server permissions were

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

Seeing what the SQL Server permissions were for a particular user

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

Seeing what SQL Server security changes were made overall

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

And that's how we can use an old backup to get both the old permissions and what has changed in the mean time. There is a caveat and that is if we're using a large database, such as for a data warehouse, this may be a very painful and time consuming approach. After all, you're looking at potentially the same space requirement as the existing database as well as the time to restore the backup.

In short, while this is doable, it is a bit painful in larger implementations. This is one of the reasons we try to audit up front.

Source Collected from MSSQLTIPS.COM