Retrieving SQL Server Permissions for Disaster Recovery

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

-- Temporary table to hold results from sp_helprotect
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:

  1. Replace 'Grant_WGO' in ProtectType with 'Grant'
  2. 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

UPDATE #TempPerms
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:

-- Build and output the permissions
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.

SELECT CASE dperms.state_desc
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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley 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



Comments For This Article




Wednesday, May 28, 2014 - 9:35:33 AM - Craig Back To Top (31972)

I've updated your script to save types as well. 

 

SELECT CASE dperms.state_desc

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 6 THEN 'TYPE::[' + typ.[name] + ']'

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.types typ

ON dperms.major_id = typ.user_type_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;


Tuesday, January 18, 2011 - 2:23:48 PM - K. Brian Kelley Back To Top (12631)

Larry, off-hand I can't think of any issue with the change. I was going for lower than DB level permissions, as the recommendation is schema level and below, but we've broken that cardinal rule where I work because it made sense to do so.


Monday, November 8, 2010 - 1:48:55 PM - Larry Hennig Back To Top (10339)

It appears that you have a filter that excludes database permissions:

   AND dperms.major_id > 0

Adjusting that filter to include an ID of zero causes the output to include database-level permissions. 

   AND dperms.major_id >= 0

Can you think of any problem with this change?


Monday, November 8, 2010 - 1:40:55 PM - Larry Hennig Back To Top (10338)

I just ran sp_helprotect in SS2005.  It listed the permissions missed by your script, though it excluded the schema permissions:

Owner Object Grantee Grantor ProtectType Action Column
. . LHTest1 dbo Grant      CONNECT .
. . LHTest1 dbo Grant      Update .
. . LHTest1 dbo Grant      VIEW DATABASE STATE .
. . LHTest1 dbo Grant      VIEW DEFINITION .
dbo Person LHTest1 dbo Grant      Delete .
dbo Person LHTest1 dbo Grant      Select JUNK
dbo Shape LHTest1 dbo Grant      Update (All+New)
. . LHTestRole1 dbo Grant      ALTER ANY ASSEMBLY .
. . LHTestRole1 dbo Grant      VIEW DATABASE STATE .
. . LHTestRole1 dbo Grant      VIEW DEFINITION .
dbo Person LHTestRole1 dbo Grant      Select (All+New)
dbo Person LHTestRole1 dbo Grant      Update JUNK
dbo Shape LHTestRole1 dbo Grant      Insert .

You script's output (with name column added and used as primary sort):

User Permissions
LHTest1 GRANT EXECUTE ON SCHEMA::[Sales] TO [LHTest1];
LHTest1 GRANT SELECT ON SCHEMA::[Sales] TO [LHTest1];
LHTest1 GRANT DELETE ON OBJECT::[dbo].[Person] TO [LHTest1];
LHTest1 GRANT SELECT ON OBJECT::[dbo].[Person] ([JUNK]) TO [LHTest1];
LHTest1 GRANT UPDATE ON OBJECT::[dbo].[Shape] TO [LHTest1];
LHTestRole1 GRANT EXECUTE ON SCHEMA::[Person] TO [LHTestRole1];
LHTestRole1 GRANT VIEW DEFINITION ON SCHEMA::[Person] TO [LHTestRole1];
LHTestRole1 GRANT UPDATE ON OBJECT::[dbo].[Person] ([JUNK]) TO [LHTestRole1];
LHTestRole1 GRANT SELECT ON OBJECT::[dbo].[Person] TO [LHTestRole1];
LHTestRole1 GRANT INSERT ON OBJECT::[dbo].[Shape] TO [LHTestRole1];

 


Monday, November 8, 2010 - 1:02:33 PM - Larry Hennig Back To Top (10337)

A great script, and a much-needed one.  However, when I tested it against database-level permissions, it failed to list them. 

In SS 2005 SP3, using SSMS, I created a custom role and used the "Securables" dialog to grant the View Any Definition, View Database State and Select for the database that the role was in.  I created a database user and granted View Any Definition, View Database State and Update for the database that the user was in.  None of these permissions appeared in the output of your script. 

NOTE: SSMS does not automatically re-display db-level permissions when you revisit the "Securables" dialog.  If you re-display the "Securables" for the user or role, only the schema permissions appear in the upper pane.  But if you re-select ("Add") the database, then the permissions are shown in the lower section, so they are being stored.

Can you please correct the script to include database-level permissions?


Wednesday, November 3, 2010 - 10:41:00 AM - Mike Eastland Back To Top (10330)

Brian - Thanks for posting this script.  I needed something similar to retain permissions in our development databases after they are refreshed from production.  I think this will work great.  Much appreciated.


Thursday, October 28, 2010 - 11:26:36 AM - Ray Giacalone Back To Top (10311)

THANX SO MUCH FOR THIS SCRIPT!!  I can leverage this to troubleshoot SQL permissions... :-]


Thursday, October 28, 2010 - 12:17:43 AM - Sam Jumper Back To Top (10307)

Thank you Brian.  This article was very helpful.















get free sql tips
agree to terms