By: K. Brian Kelley | Comments (6) | Related: > Auditing and Compliance
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 levelSeeing 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
- Learn how to use DDL triggers to track all database changes.
- Read up on how to create a backup from the command line.
- Implement auditing and tracking using server side traces.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
