By: K. Brian Kelley | Updated: 2010-10-27 | Comments (8) | Related: > Security
Problem
I'm needing to audit the permissions in my databases, but I want to script them out so I have something to run in case of a recovery situation. I've got the logins, roles, and users handled, but it's the permissions that I want to extract. How can I do this easily?
Solution
The solution we choose is dependent on the version of SQL Server we're working with. Since SQL Server 2005 changed the security model by introducing securables, we have to take different approaches depending on whether we're talking SQL Server 2000 or SQL Server 2005 and above. Let's start with SQL Server 2000.
SQL Server 2000
SQL Server 2000 has a great system stored procedure, sp_helprotect, which extracts the basic permissions for us. One of the things it does easily is convert the bitmap found in sysprotects for column level permissions so we don't have to re-do the work ourselves. However, sp_helprotect is a stored procedure, so that means we'll need to get its output into a temporary table so we can work with it. Therefore, the first step is to build the temporary table we'll be using:
CREATE TABLE #TempPerms (
[Owner] sysname NOT NULL,
[Object] sysname NOT NULL,
[Grantee] sysname NOT NULL,
[Grantor] sysname NOT NULL,
[ProtectType] NVARCHAR(9) NOT NULL,
[Action] NVARCHAR(100) NOT NULL,
[Column] NVARCHAR(300) NOT NULL,
[WithGrant] VARCHAR(18) NULL DEFAULT ''
);
-- Let sp_helprotect do the work for us
INSERT INTO #TempPerms
([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])
EXEC sys.sp_helprotect;
After the temporary table is built, the next step is writing a query that will build the T-SQL statements to execute should we need to restore the permissions back to what they were when we audited them. Now there is one little trick we'll implement, and that's in cases where a GRANT was done with the WITH GRANT OPTION. This is reported by sp_helprotect as Grant_WGO, so we'll need to do two things:
- Replace 'Grant_WGO' in ProtectType with 'Grant'
- Populate the WithGrant column with ' WITH GRANT OPTION' for cases where ProtectType was 'Grant_WGO'
We can do this simply with an UPDATE statement like so:
-- Handle situation where permission was GRANT and WITH GRANT OPTION was used
SET [ProtectType] = 'Grant ', [WithGrant] = ' WITH GRANT OPTION'
WHERE [ProtectType] = 'Grant_WGO';
And now it's a relatively simple matter to extract the permissions. We'll have to use a CASE in the SELECT based on the Owner, because if the Owner is '.' that means it's a database level permission. We'll also have to check to see if the Column is set to (All+New), (All), or specifies columns and that requires a nested CASE. This may sound a bit complicated, but it's really not. Here's the query that pulls the information out of the temporary table and builds the recovery script:
SELECT
CASE [Owner]
WHEN '.' THEN ProtectType + ' ' + [Action] + ' TO [' + [Grantee] + ']' + [WithGrant] + ';'
ELSE CASE [Column]
WHEN '(All+New)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
WHEN '(All)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
WHEN '.' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
ELSE ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] ([' + [Column] + ']) TO [' + Grantee + ']' + [WithGrant] + ';'
END
END AS 'Permissions'
FROM #TempPerms;
SQL Server 2005 and higher
SQL Server 2005 and higher is a bit harder. We have to worry about more types of permissions as well as permissions like SELECT being applied at both the database and schema levels, which we didn't have to worry about in SQL Server 2000. Also, we don't have a nice stored procedure like sp_helprotect to do all of the heavy lifting for us. What we do have is a slew of catalog views from which we can extract the information. The basis of it all, though, is sys.database_permissions along with sys.database_principals and sys.objects. Let's look at the query first and then walk through it.
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE state_desc
END
+ ' ' + permission_name + ' ON ' +
CASE dperms.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN
CASE dperms.minor_id
WHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'
ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'
END
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'
WHEN 4 THEN 'USER::[' + USER_NAME(major_id) + ']'
WHEN 24 THEN 'SYMMETRIC KEY::[' + symm.[name] + ']'
WHEN 25 THEN 'CERTIFICATE::[' + certs.[name] + ']'
WHEN 26 THEN 'ASYMMETRIC KEY::[' + asymm.[name] +']'
END
+ ' TO [' + dprins.[name] + ']' +
CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END COLLATE database_default AS 'Permissions'
FROM sys.database_permissions dperms
INNER JOIN sys.database_principals dprins
ON dperms.grantee_principal_id = dprins.principal_id
LEFT JOIN sys.columns col
ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id
LEFT JOIN sys.objects obj
ON dperms.major_id = obj.object_id
LEFT JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
LEFT JOIN sys.asymmetric_keys asymm
ON dperms.major_id = asymm.asymmetric_key_id
LEFT JOIN sys.symmetric_keys symm
ON dperms.major_id = symm.symmetric_key_id
LEFT JOIN sys.certificates certs
ON dperms.major_id = certs.certificate_id
WHERE dperms.type <> 'CO'
AND dperms.major_id > 0;
The first thing to note is that the different classes mean we have to modify our T-SQL statements accordingly. I've chosen the major ones: database, schema, objects, users, and the encryption objects of certificates and keys. This causes us to do some extra joins which makes the FROM portion look nastier than it really is. This is all to be able to get the names of the different securables to match with the classes. Otherwise, the structure is pretty much straight forward with two exceptions, which I'll cover in a moment. One thing that really helps is that some of the catalog view columns are human readable, like state_desc. So we can use this to our advantage in building the scripts.
The two exceptions is how column-level permissions are handled and the fact that if you look in sys.database_permissions, you'll notice that there are major_id values that are less than zero. In the case of column level permissions, instead of a bitmap, each column is reported separately. The major_id corresponds to the object_id in sys.objects and the minor_id corresponds to the column_id in sys.columns. If minor_id is 0, that means the whole table or view. But if minor_id has a value greater than 0, that we have to build out the T-SQL to reflect the column level permissions. That's the reason for the case statement on minor_id. The second exception is with those negative values for major_id. Those correspond to system objects, so I've excluded them because only in the rarest cases would we be altering permissions against system objects. If you're wondering about excluding where type = 'CO' that corresponds to having the ability to connect to the database. Normally this is handled with the CREATE USER statement, so I've excluded it in the query.
Next Steps
- Read these additional tips related to security.
- Add this process to your disaster recovery procedures. Along with having database backups, having scripts to recreate permissions may come in handy.
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
Article Last Updated: 2010-10-27
