Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using a Backup to Find SQL Server Security Changes


By:   |   Read Comments (6)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, September 17, 2012 - 4:50:25 PM - Janusz Rokicki Back To Top

 

Good tip, but it's crying over spilt milk. Permission baseline should be part of release procedure in first place. Code contains implicit assumptions that should be revealed:

- IDs (objects, principals) must be the same in both databases. Not supported cases: release processes with 'drop if exists'-create-grant pattern, code-first, column ordinal position changes etc.

- when objects are added or removed list of permissions will be polluted (by design but not obvious)

- databases must not have permissions with column granularity (sys.database_permissions.minor_is IS NULL is missing)

Also query contains multiple database names references - SQLCMD mode should help.

 


Wednesday, September 12, 2012 - 9:04:48 PM - Gene Wirchenko Back To Top
Sure, but one could run scripts against both databases to determine what one wanted to determine -- permissions, indexes, whatever -- without a big hit on storage.

Wednesday, September 12, 2012 - 3:39:48 PM - K. Brian Kelley Back To Top

That's the physical design of the database, with respect to how you allocate files and filegroups. That's beyond the ability of an audit script to determine, unfortunately.


Tuesday, September 11, 2012 - 7:02:03 PM - Gene Wirchenko Back To Top
That would seem to be a very good idea for an enhancement. I can see it as being very useful for establishing test systems or for comparing two systems that are supposed to be the same.

Tuesday, September 11, 2012 - 4:58:45 PM - K. Brian Kelley Back To Top

It depends. If it's all in one data file (.MDF) as is the default, no. However, you could attach using a 3rd party tool that supports attaching backups as databases.

 


Tuesday, September 11, 2012 - 12:32:03 PM - Gene Wirchenko Back To Top
For a situation like this, can you not restore all but the table data?

Learn more about SQL Server tools