Home » Blog » How to Script Database Permissions

How to Script Database Permissions

2014-10-07 - DMVs & CVs, General, Security, Security

If you are responsible for permission management in SQL Server, you will eventually need to script out permissions. For example, if you want to change the owner of a securable, SQL Server drops all permissions granted on that securable. Therefore, it is a good idea to script out the current permissions beforehand, so you can recreate them afterwards. But even if you only want to take a look at the current permission set, generating a script can be helpful.

The sys.database_permissions Catalog View

The problem is, that SQL Server does not have a good way of scripting out permissions let alone all permissions granted on a specific securable. There is however the sys.database_permissions catalog view, that returns a row for each granted or denied permission in the current database. The only disadvantage of sys.database_permissions is that most of the information is encoded in numbers. Worse, you cannot just join to a single table, say sys.objects, to translate those numbers into names, as for example just the securable's id can reference any of 21 different catalog views.

To demonstrate, let us look at the following set of example permissions:

[sql] CREATE USER TestUser1 WITHOUT LOGIN;
CREATE USER TestUser2 WITHOUT LOGIN;
CREATE ROLE TestRole1;
CREATE APPLICATION ROLE TestAppRole1 WITH PASSWORD = '********';
GO
CREATE SCHEMA TestSchema1;
GO
CREATE TYPE TestSchema1.TestType1 FROM CHAR(5);
GO
CREATE TABLE TestSchema1.tst(id INT,col1 INT, col2 TestSchema1.TestType1);
INSERT INTO TestSchema1.tst VALUES(42,17,'12345');

CREATE QUEUE TestSchema1.TestQueue1;
GO
CREATE ASSEMBLY TestAssembly1
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103000F1188520000000000000000E00002210B0108000006000000060000000000007E2400000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000298E0000030040850000100000100000000010000010000000000000100000000000000000000000302400004B00000000400000A802000000000000000000000000000000000000006000000C000000B82300001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000084040000002000000006000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000080000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000C0000000000000000000000000000400000420000000000000000000000000000000060240000000000004800000002000500D0200000E80200000B0000000000000000000000000000005020000080000000000000000000000000000000000000000000000000000000000000000000000009E72C31E7F17642C6F0559F050B5A6B0CE87083F2E30744C3F09A9E79FEDC4F487D4400E4F6E6A8D2B80B994AB75C0AA7909E6A77356EF0FF206CD2DCB566EC977FB58416C25FB6D2F95E9CF681409B7D90F18AFA5536C338DEC3FC0D2DDF4DF40F35DF5A07EBC273070D0212AC1715916CA40FEFC26BAA5392A5203D9A8D0742534A4201000100000000000C00000076322E302E35303732370000000005006C000000B4000000237E000020010000C400000023537472696E677300000000E40100000800000023555300EC010000100000002347554944000000FC010000EC00000023426C6F620000000000000002000001071400000900000000FA2533001600000100000004000000010000000300000003000000010000000100000000000A0001000000000006003300200007004700000006007C005C0006009C005C000000000001000000000001000100090056000A001900560010002100560015002E000B00BB002E001300C4002E001B00CD00048000000000000000000000010000001900BA00000002000000000000000000000001001700000000000000003C4D6F64756C653E007453514C744B65792E646C6C006D73636F726C69620053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F646573002E63746F720053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007453514C744B65790000000320000000000014ABB0F84DDBC64C9E984A68F97750500008B77A5C561934E08905200101110904200101080320000180A00024000004800000940000000602000000240000525341310004000001000100590AB8C4CF2A26FA41954EEAABE1E3D152A84C81F41E1FAD58EAE59DFB9D7D3520D36FDFC23567120AF4B46ACC235A150B34CF341AD40147E9DD4F11A1A7A8D20664924F46776FD00AA300F2E09F7BFBE5583FFFBB233B24401A3C0894E805BA8BE5451FDBD81AD24E0897512A842B08E1FC09CC6F35B3B21B5F927687887AC40801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000F118852000000000200000059000000D4230000D4050000525344531DEA0245398EB148B29FBF2957A2FB8E06000000433A5C446174615C73766E5C7453514C745C7453514C74434C525C7453514C744B65795C6F626A5C7838365C52656C656173655C7453514C744B65792E706462000000005824000000000000000000006E240000002000000000000000000000000000000000000000000000602400000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000D00010049006E007400650072006E0061006C004E0061006D00650000007400530051004C0074004B00650079002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000007400530051004C0074004B00650079002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000803400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE XML SCHEMA COLLECTION TestSchema1.TestXMLSchemaCollection1 AS N'';
CREATE MESSAGE TYPE TestMessageType1;
CREATE SERVICE TestService1 ON QUEUE TestSchema1.TestQueue1;
CREATE CONTRACT TestContract1(TestMessageType1 SENT BY ANY);
CREATE REMOTE SERVICE BINDING TestRSBinding1 TO SERVICE 'TestService1' WITH USER = TestUser1;
CREATE ROUTE TestRoute1 WITH ADDRESS = 'TCP://localhost';
CREATE FULLTEXT CATALOG TestFTCatalog1;
CREATE FULLTEXT STOPLIST TestStopList1;
CREATE SEARCH PROPERTY LIST TestSPList1;
GO
CREATE SYMMETRIC KEY TestSymmetricKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='********';
CREATE ASYMMETRIC KEY TestAssymmetricKey1 FROM ASSEMBLY TestAssembly1;
CREATE CERTIFICATE TestCertificate1 ENCRYPTION BY PASSWORD = '********' WITH SUBJECT='Subject';
GO
-----------------------------------------------
GRANT SELECT TO [TestUser1]; --no securable -> entire database
GRANT INSERT ON SCHEMA::[TestSchema1] TO [TestUser1];
GRANT UPDATE ON OBJECT::[TestSchema1].[tst] TO [TestUser1] WITH GRANT OPTION;
GRANT SELECT ON OBJECT::[TestSchema1].[tst]([col1]) TO [TestUser2];
GRANT IMPERSONATE ON USER::[TestUser2] TO [TestUser1];
GRANT TAKE OWNERSHIP ON ROLE::[TestRole1] TO [TestUser1];
GRANT ALTER ON APPLICATION ROLE::[TestAppRole1] TO [TestUser1];
GRANT VIEW DEFINITION ON ASSEMBLY::TestAssembly1 TO [TestUser2] WITH GRANT OPTION;
GRANT REFERENCES ON TYPE::[TestSchema1].[TestType1] TO [TestUser2];
GRANT EXECUTE ON XML SCHEMA COLLECTION::[TestSchema1].[TestXMLSchemaCollection1] TO [TestUser1];
GRANT ALTER ON CONTRACT::[TestContract1] TO [TestUser2];
DENY VIEW DEFINITION ON MESSAGE TYPE::[TestMessageType1] TO [TestUser1];
DENY ALTER ON REMOTE SERVICE BINDING::[TestRSBinding1] TO [TestUser2];
DENY TAKE OWNERSHIP ON ROUTE::[TestRoute1] TO TestUser2;
GRANT CONTROL ON SERVICE::[TestService1] TO TestUser2;
GRANT REFERENCES ON FULLTEXT CATALOG::[TestFTCatalog1] TO [TestUser1];
GRANT REFERENCES ON FULLTEXT STOPLIST::[TestStopList1] TO [TestUser1] WITH GRANT OPTION;
GRANT ALTER ON SEARCH PROPERTY LIST::[TestSPList1] TO [TestUser1];
GRANT CONTROL ON ASYMMETRIC KEY::[TestAssymmetricKey1] TO [TestUser1];
GRANT REFERENCES ON CERTIFICATE::[TestCertificate1] TO [TestUser1];
GRANT ALTER ON SYMMETRIC KEY::[TestSymmetricKey1] TO [TestUser1];
DENY SELECT ON OBJECT::sys.objects TO [TestUser2];
[/sql]

The above T-SQL script creates a bunch of securables in the current database and then grants different permissions to the two users TestUser1 and TestUser2. A straight SELECT from sys.database_permissino now returns the following information:

You can see that only the securable-type and the privilege itself are readable. All other columns return ids.

The Database Permissions Query

To turn the information returned by sys.database_permissions into GRANT statements, we need to go through some join logic:

[sql] SELECT CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE P.state_desc END AS cmd_state,
P.permission_name,
'ON '+ CASE P.class_desc
WHEN 'DATABASE' THEN 'DATABASE::'+QUOTENAME(DB_NAME())
WHEN 'SCHEMA' THEN 'SCHEMA::'+QUOTENAME(S.name)
WHEN 'OBJECT_OR_COLUMN' THEN 'OBJECT::'+QUOTENAME(OS.name)+'.'+QUOTENAME(O.name)+
CASE WHEN P.minor_id <> 0 THEN '('+QUOTENAME(C.name)+')' ELSE '' END
WHEN 'DATABASE_PRINCIPAL' THEN
CASE PR.type_desc
WHEN 'SQL_USER' THEN 'USER'
WHEN 'DATABASE_ROLE' THEN 'ROLE'
WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
END +'::'+QUOTENAME(PR.name)
WHEN 'ASSEMBLY' THEN 'ASSEMBLY::'+QUOTENAME(A.name)
WHEN 'TYPE' THEN 'TYPE::'+QUOTENAME(TS.name)+'.'+QUOTENAME(T.name)
WHEN 'XML_SCHEMA_COLLECTION' THEN 'XML SCHEMA COLLECTION::'+QUOTENAME(XSS.name)+'.'+QUOTENAME(XSC.name)
WHEN 'SERVICE_CONTRACT' THEN 'CONTRACT::'+QUOTENAME(SC.name)
WHEN 'MESSAGE_TYPE' THEN 'MESSAGE TYPE::'+QUOTENAME(SMT.name)
WHEN 'REMOTE_SERVICE_BINDING' THEN 'REMOTE SERVICE BINDING::'+QUOTENAME(RSB.name)
WHEN 'ROUTE' THEN 'ROUTE::'+QUOTENAME(R.name)
WHEN 'SERVICE' THEN 'SERVICE::'+QUOTENAME(SBS.name)
WHEN 'FULLTEXT_CATALOG' THEN 'FULLTEXT CATALOG::'+QUOTENAME(FC.name)
WHEN 'FULLTEXT_STOPLIST' THEN 'FULLTEXT STOPLIST::'+QUOTENAME(FS.name)
WHEN 'SEARCH_PROPERTY_LIST' THEN 'SEARCH PROPERTY LIST::'+QUOTENAME(RSPL.name)
WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY::'+QUOTENAME(SK.name)
WHEN 'CERTIFICATE' THEN 'CERTIFICATE::'+QUOTENAME(CER.name)
WHEN 'ASYMMETRIC_KEY' THEN 'ASYMMETRIC KEY::'+QUOTENAME(AK.name)
END COLLATE Latin1_General_100_BIN AS securable,
'TO '+QUOTENAME(DP.name) AS grantee,
CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'WITH GRANT OPTION' ELSE '' END AS grant_option,
'AS '+QUOTENAME(G.name) AS grantor
FROM sys.database_permissions AS P
LEFT JOIN sys.schemas AS S
ON P.major_id = S.schema_id
LEFT JOIN sys.all_objects AS O
JOIN sys.schemas AS OS
ON O.schema_id = OS.schema_id
ON P.major_id = O.object_id
LEFT JOIN sys.types AS T
JOIN sys.schemas AS TS
ON T.schema_id = TS.schema_id
ON P.major_id = T.user_type_id
LEFT JOIN sys.xml_schema_collections AS XSC
JOIN sys.schemas AS XSS
ON XSC.schema_id = XSS.schema_id
ON P.major_id = XSC.xml_collection_id
LEFT JOIN sys.columns AS C
ON O.object_id = C.object_id
AND P.minor_id = C.column_id
LEFT JOIN sys.database_principals AS PR
ON P.major_id = PR.principal_id
LEFT JOIN sys.assemblies AS A
ON P.major_id = A.assembly_id
LEFT JOIN sys.service_contracts AS SC
ON P.major_id = SC.service_contract_id
LEFT JOIN sys.service_message_types AS SMT
ON P.major_id = SMT.message_type_id
LEFT JOIN sys.remote_service_bindings AS RSB
ON P.major_id = RSB.remote_service_binding_id
LEFT JOIN sys.services AS SBS
ON P.major_id = SBS.service_id
LEFT JOIN sys.routes AS R
ON P.major_id = R.route_id
LEFT JOIN sys.fulltext_catalogs AS FC
ON P.major_id = FC.fulltext_catalog_id
LEFT JOIN sys.fulltext_stoplists AS FS
ON P.major_id = FS.stoplist_id
LEFT JOIN sys.registered_search_property_lists AS RSPL
ON P.major_id = RSPL.property_list_id
LEFT JOIN sys.asymmetric_keys AS AK
ON P.major_id = AK.asymmetric_key_id
LEFT JOIN sys.certificates AS CER
ON P.major_id = CER.certificate_id
LEFT JOIN sys.symmetric_keys AS SK
ON P.major_id = SK.symmetric_key_id
JOIN sys.database_principals AS DP
ON P.grantee_principal_id = DP.principal_id
JOIN sys.database_principals AS G
ON P.grantor_principal_id = G.principal_id
WHERE P.grantee_principal_id IN (USER_ID('TestUser1'), USER_ID('TestUser2'));
[/sql]

This rather complex looking query left-joins to all catalog views for the different securable types and then uses a case statement to return the correct one:

The result consists of six separate columns, each holding a piece of the grant statement. The complete result returned by above query is below:

I use this query often in my presentations and classes, so the output is designed with readability in mind. To generate executable statements, you just have to concatenate the six columns together (in order). Keep in mind that all columns are important, even if you might not usually find them in a GRANT statement.

Feel free to adjust the query to your needs. Also make sure to remove or adapt the WHERE clause if you want to use it in your own environments. In case you executed the above demo script on your server, here are the statements required to clean everything up:

[sql] DROP FULLTEXT CATALOG TestFTCatalog1;
DROP FULLTEXT STOPLIST TestStopList1;
DROP SEARCH PROPERTY LIST TestSPList1;
DROP ROUTE TestRoute1;
DROP REMOTE SERVICE BINDING TestRSBinding1;
DROP CONTRACT TestContract1;
DROP SERVICE TestService1;
DROP MESSAGE TYPE TestMessageType1;
GO
DROP CERTIFICATE TestCertificate1;
DROP SYMMETRIC KEY TestSymmetricKey1;
DROP ASYMMETRIC KEY TestAssymmetricKey1;
GO
DROP QUEUE TestSchema1.TestQueue1;
DROP TABLE TestSchema1.tst;
GO
DROP TYPE TestSchema1.TestType1;
GO
DROP XML SCHEMA COLLECTION TestSchema1.TestXMLSchemaCollection1;
GO
DROP SCHEMA TestSchema1;
GO
DROP ASSEMBLY TestAssembly1;
GO
DROP USER TestUser1;
DROP USER TestUser2;
DROP ROLE TestRole1;
DROP APPLICATION ROLE TestAppRole1;
[/sql]