SHARE
TWEET

"clr strict security" - Solution 2

SqlQuantumLeap Aug 20th, 2017 (edited) 180 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  --------- TEST 2 FOR SQLCLR on SQL Server 2017 (Windows and Linux) ---------
  3.  
  4. By: Solomon Rutzky
  5. On: 2017-08-10
  6. Location: https://pastebin.com/2nw9Kyfe
  7. Related blog post: https://SqlQuantumLeap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/
  8.  
  9. Description: This script shows a technique for deploying SQLCLR Assemblies to SQLCLR 2017
  10.              while both keeping "clr strict security" enabled and "TRUSTWORTHY" disabled,
  11.              AND while being fulled contained within a SQL script, AND working with SSDT.
  12.  
  13.              The trick is to override the default SSDT build process to add a step to sign
  14.              the DLL with a certificate before the DLL is pulled into a deployment script.
  15.  
  16.              For a detailed, step-by-step explanation, please see the relatd blog post
  17.              noted above.
  18. */
  19.  
  20.  
  21.  
  22. USE [master];
  23. SET NOCOUNT ON;
  24. GO
  25.  
  26. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NULL)
  27. BEGIN
  28.     PRINT 'Creating Database...';
  29.     CREATE DATABASE [SQLCLRvsClrStrictSecurity]
  30.         COLLATE Latin1_General_100_CI_AS;
  31. END;
  32.  
  33. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NOT NULL)
  34. BEGIN
  35.     PRINT 'Altering Database (make sure RECOVERY=SIMPLE and TRUSTWORTHY=OFF)...';
  36.     ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  37.         SET RECOVERY SIMPLE,
  38.         TRUSTWORTHY OFF;
  39. END;
  40.  
  41.  
  42. PRINT 'Making sure that SQLCLR is enabled...';
  43. IF (EXISTS(
  44.             SELECT *
  45.             FROM   sys.configurations sc
  46.             WHERE  sc.[configuration_id] = 1562 -- "clr enabled"
  47.             AND    sc.[value_in_use] = 0
  48.     ))
  49. BEGIN
  50.     PRINT 'Enabling SQLCLR...';
  51.     EXEC sp_configure N'clr enabled', 1; RECONFIGURE;
  52. END;
  53.  
  54. PRINT 'Making sure that SQLCLR "strict security" is enabled...';
  55. IF (EXISTS(
  56.             SELECT *
  57.             FROM   sys.configurations sc
  58.             WHERE  sc.[configuration_id] = 518 -- "show advanced options"
  59.             AND    sc.[value_in_use] = 0
  60.     ))
  61. BEGIN
  62.     PRINT ' Showing advanced options...';
  63.     EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
  64. END;
  65.  
  66.  
  67. IF (EXISTS(
  68.             SELECT *
  69.             FROM   sys.configurations sc
  70.             WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
  71.             AND    sc.[value_in_use] = 0
  72.     ))
  73. BEGIN
  74.     PRINT ' Enabling CLR strict security...';
  75.     EXEC sp_configure N'clr strict security', 1; RECONFIGURE;
  76. END;
  77.  
  78.  
  79.  
  80.  
  81.  
  82. IF (SUSER_ID(N'SQL2017-ClrStrictSecurity2-Login') IS NULL)
  83. BEGIN
  84.     PRINT 'Permission Login does NOT exist:';
  85.  
  86.     IF (CERT_ID(N'SQL2017-ClrStrictSecurity2-Cert') IS NULL)
  87.     BEGIN
  88.         PRINT ' Creating Certificate...';
  89.         CREATE CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert]
  90. FROM BINARY = 0x\
  91. 3082037930820265A00302010202109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D\
  92. 05003040310B3009060355040613025553311B3019060355040A131253716C5175616E74756D4C65\
  93. 61702E636F6D311430120603550403130B53514C434C5220436572743020170D3137303831383033\
  94. 313030345A180F32303939313233313034303030305A3040310B3009060355040613025553311B30\
  95. 19060355040A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C\
  96. 434C52204365727430820122300D06092A864886F70D01010105000382010F003082010A02820101\
  97. 00CCEA3F8D614CE7AB2B52E39301A9D9E2A7828473EC10D8748CF0BB32DDF6ED1423E56C0FC36DAA\
  98. 995D54BD7194BF3BC71B1931111706DF162ED5188017B0B570DDF9AFFD3E531E266BFADD02220253\
  99. 53277BAC965E89DB4645438A973256897E0D2644C24CB3C61AC0011FC7CDDFE06C1234C1C0A60158\
  100. 6952FF2033C58F8A519AD1D71B35A6975E1F846A83D1F90D1602A372CC8EB697240E9E38852BBB96\
  101. CB78FE296B57DE401AAFD958EDDF59894E8C0A42B95A77BE43C1B893CE312824B68C931F9893639A\
  102. 41BF99E68AD6F2AA2AEC58AA5CC067EF8FF0AD7BE9A1AA44B48F8338BBD324C2D162DA30356FD461\
  103. BF2FD388EDDFDD5990682379EF18A33C4D0203010001A375307330710603551D01046A30688010B7\
  104. C292D45B0F3FDDBF55241F2FD6F2CFA1423040310B3009060355040613025553311B301906035504\
  105. 0A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C522043\
  106. 65727482109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D050003820101006AF2D8\
  107. F382D55034DE7715B0F5536669914C6B256344B7343B0B099B32B4759E77B367CA19A3B3FBC7DBCF\
  108. 913FB022DD5D4F83EB6792D58B63AF015767F0BC059D24D3E019880A95DEA9E72BD462302806CF9B\
  109. FB1C9A3E3BAC4D612889ACF4839AC0FE7F60625FA33DABA6F0FCD6EBF88401712EF3DA505092CBB5\
  110. ACCDC510258F3003868DC15C0E5374B01E6BF555972E3F99C789F1E14B171CE508D895CDD71AB6FD\
  111. 8D9F61962A3E1D89E0E748E3C5505AF21E0DF6D95FEA86E87D5DED7D00A669FC8CAB472A93F993BB\
  112. B21636AEDE56F747E11FCF69F96D4C87732C5BE3780E01C772F3BD0938DD5A440BDCC84680839572\
  113. 8AF10C95C6F8E6714DDAFA19B2
  114.     END;
  115.  
  116.     IF (SUSER_ID(N'SQL2017-ClrStrictSecurity2-Login') IS NULL)
  117.     BEGIN
  118.         PRINT ' Creating permissions Login...';
  119.         CREATE LOGIN [SQL2017-ClrStrictSecurity2-Login]
  120.             FROM CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];
  121.     END;
  122. END;
  123.  
  124. -- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity2-Login];
  125. GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity2-Login]; -- REQUIRED!!!!
  126.  
  127.  
  128. --DROP LOGIN [SQL2017-ClrStrictSecurity2-Login];
  129. --DROP CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];
  130.  
  131.  
  132. -- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
  133. SELECT *
  134. FROM   sys.configurations sc
  135. WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
  136.  
  137. SELECT [name], [is_trustworthy_on], [collation_name]
  138. FROM sys.databases WHERE [database_id] = DB_ID(N'SQLCLRvsClrStrictSecurity');
  139.  
  140.  
  141. GO
  142. USE [SQLCLRvsClrStrictSecurity];
  143. -- DROP FUNCTION [dbo].[String_PadLeft];
  144. -- DROP ASSEMBLY [SQL2017_ClrStrictSecurity2];
  145.  
  146.  
  147. IF (ASSEMBLYPROPERTY(N'SQL2017_ClrStrictSecurity2', N'MvID') IS NULL)
  148. BEGIN
  149.     PRINT 'Creating [SQL2017_ClrStrictSecurity2] Assembly...';
  150.     CREATE ASSEMBLY [SQL2017_ClrStrictSecurity2]
  151.         AUTHORIZATION [dbo]
  152.         FROM 0x\
  153. 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
  154. 0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
  155. 69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
  156. 2400000000000000504500004C01030082F498590000000000000000E00002210B010B00000A0000\
  157. 0008000000000000CE28000000200000004000000000001000200000000200000400000000000000\
  158. 06000000000000000080000000020000247B00000300608500001000001000000000100000100000\
  159. 00000000100000000000000000000000802800004B00000000400000980400000000000000000000\
  160. 00140000D8050000006000000C000000482700001C00000000000000000000000000000000000000\
  161. 00000000000000000000000000000000000000000000000000200000080000000000000000000000\
  162. 082000004800000000000000000000002E74657874000000D408000000200000000A000000020000\
  163. 000000000000000000000000200000602E72737263000000980400000040000000060000000C0000\
  164. 000000000000000000000000400000402E72656C6F6300000C000000006000000002000000120000\
  165. 0000000000000000000000004000004200000000000000000000000000000000B028000000000000\
  166. 48000000020005007C200000CC060000010000000000000000000000000000000000000000000000\
  167. 0000000000000000000000000000000000000000000000000000000000000000860F00280F00000A\
  168. 0F02281000000A03166A6F1100000A6F1200000A731300000A2A1E02281400000A2A000042534A42\
  169. 01000100000000000C00000076342E302E33303331390000000005006C00000008020000237E0000\
  170. 74020000E402000023537472696E6773000000005805000008000000235553006005000010000000\
  171. 2347554944000000700500005C01000023426C6F6200000000000000020000014715000009000000\
  172. 00FA2533001600000100000014000000020000000200000003000000140000000C00000001000000\
  173. 0200000000000A0001000000000006004E0047000A00760061000A00800061000A00890061000600\
  174. D700C5000600EE00C50006000B01C50006002A01C50006004301C50006005C01C50006007701C500\
  175. 06009201C5000600AB01C5000600C401C5000600F401E1013F000802000006003702170206005702\
  176. 17020A00AB0290020600D30247000000000001000000000001000100010010002900000005000100\
  177. 0100502000000000960092000A0001007220000000008618A1001500040000000100A70000000200\
  178. B30000000300BB002900A10019003100A10019003900A10019004100A10019004900A10019005100\
  179. A10019005900A10019006100A10019006900A10019007100A10019007900A1001E008900A1002400\
  180. 9100A10015009900A10015001100C00241002100C00245001900CA024900A100DA024E001100A100\
  181. 19000900A10015002000730029002E002B00B5002E00130074002E001B0099002E0023009F002E00\
  182. 0B0054002E003300E1002E003B0099002E0053001D012E005B002A012E00630033012E006B003C01\
  183. 0480000002000600070000000000000000007502000004000000000000000000000001003E000000\
  184. 000004000000000000000000000001005500000000000000003C4D6F64756C653E0053514C323031\
  185. 375F436C725374726963745365637572697479322E646C6C0055736572446566696E656446756E63\
  186. 74696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E44617461\
  187. 0053797374656D2E446174612E53716C54797065730053716C537472696E670053716C4368617273\
  188. 0053716C496E74333200537472696E675F5061644C656674002E63746F7200496E70757453747269\
  189. 6E67005061644368617200506164416D6F756E740053797374656D2E5265666C656374696F6E0041\
  190. 7373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F\
  191. 6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574\
  192. 6500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475\
  193. 637441747472696275746500417373656D626C79436F707972696768744174747269627574650041\
  194. 7373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572\
  195. 6541747472696275746500417373656D626C7956657273696F6E4174747269627574650041737365\
  196. 6D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F7374\
  197. 6963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053\
  198. 797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C617469\
  199. 6F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C\
  200. 6974794174747269627574650053514C323031375F436C7253747269637453656375726974793200\
  201. 4D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174\
  202. 74726962757465006765745F56616C7565006765745F4974656D00537472696E67005061644C6566\
  203. 740000000003200000000000F41C3A78FF4C4A49B5F1F9B95DE8B0F70008B77A5C561934E0890A00\
  204. 0311091109120D111103200001042001010E0520010111410420010108170100010054020F497344\
  205. 657465726D696E6973746963010320000E03200008042001030A0520020E08031F01001A53514C32\
  206. 3031375F436C7253747269637453656375726974793200002401001F68747470733A2F2F53716C51\
  207. 75616E74756D4C6561702E636F6D2F3230313700000501000000001501001053716C205175616E74\
  208. 756D204C65617000002B01002653514C20536572766572203230313720767320434C522073747269\
  209. 637420736563757269747900003B010036436F7079726967687420286329203230313720536F6C6F\
  210. 6D6F6E2052757A6B792E20416C6C207269676874732072657365727665642E00000C010007332E38\
  211. 2E392E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E45\
  212. 7863657074696F6E5468726F777301000000000082F4985900000000020000001C01000064270000\
  213. 64090000525344535AC21935AE54774C9B9CF9BD052243AB08000000633A5C53514C5175616E7475\
  214. 6D4C6561705C50726F6A656374735C426C6F67546F706963735C313830335C53514C323031375F43\
  215. 6C725374726963745365637572697479325C53514C323031375F436C725374726963745365637572\
  216. 697479325C6F626A5C52656C656173655C53514C323031375F436C72537472696374536563757269\
  217. 7479322E706462000000000000000000000000000000000000000000000000000000000000000000\
  218. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  219. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  220. 0000000000000000A82800000000000000000000BE28000000200000000000000000000000000000\
  221. 0000000000000000B02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C\
  222. 6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000\
  223. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  224. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  225. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  226. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  227. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  228. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  229. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  230. 00000000000001001000000018000080000000000000000000000000000001000100000030000080\
  231. 00000000000000000000000000000100000000004800000058400000400400000000000000000000\
  232. 400434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000\
  233. BD04EFFE00000100080003000000090008000300000009003F000000000000000400000002000000\
  234. 000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00\
  235. 000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004\
  236. A0030000010053007400720069006E006700460069006C00650049006E0066006F0000007C030000\
  237. 010030003000300030003000340062003000000058002000010043006F006D006D0065006E007400\
  238. 73000000680074007400700073003A002F002F00530071006C005100750061006E00740075006D00\
  239. 4C006500610070002E0063006F006D002F003200300031003700000044001100010043006F006D00\
  240. 700061006E0079004E0061006D00650000000000530071006C0020005100750061006E0074007500\
  241. 6D0020004C006500610070000000000060001B000100460069006C00650044006500730063007200\
  242. 69007000740069006F006E0000000000530051004C0032003000310037005F0043006C0072005300\
  243. 74007200690063007400530065006300750072006900740079003200000000003000080001004600\
  244. 69006C006500560065007200730069006F006E000000000033002E0038002E0039002E0030000000\
  245. 60001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C003200\
  246. 3000310037005F0043006C0072005300740072006900630074005300650063007500720069007400\
  247. 790032002E0064006C006C00000000009400370001004C006500670061006C0043006F0070007900\
  248. 72006900670068007400000043006F00700079007200690067006800740020002800630029002000\
  249. 3200300031003700200053006F006C006F006D006F006E002000520075007A006B0079002E002000\
  250. 41006C006C0020007200690067006800740073002000720065007300650072007600650064002E00\
  251. 0000000068001F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00\
  252. 65000000530051004C0032003000310037005F0043006C0072005300740072006900630074005300\
  253. 650063007500720069007400790032002E0064006C006C0000000000700027000100500072006F00\
  254. 64007500630074004E0061006D00650000000000530051004C002000530065007200760065007200\
  255. 20003200300031003700200076007300200043004C00520020007300740072006900630074002000\
  256. 7300650063007500720069007400790000000000340008000100500072006F006400750063007400\
  257. 560065007200730069006F006E00000033002E0038002E0039002E00300000003800080001004100\
  258. 7300730065006D0062006C0079002000560065007200730069006F006E00000032002E0036002E00\
  259. 37002E00300000000000000000000000000000000000000000000000000000000000000000000000\
  260. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  261. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  262. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  263. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  264. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  265. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  266. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  267. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  268. 0000000000000000002000000C000000D03800000000000000000000000000000000000000000000\
  269. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  270. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  271. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  272. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  273. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  274. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  275. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  276. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  277. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  278. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  279. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  280. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  281. D805000000020200308205C506092A864886F70D010702A08205B6308205B2020101310B30090605\
  282. 2B0E03021A0500304C060A2B060104018237020104A03E303C3017060A2B06010401823702010F30\
  283. 09030100A004A20280003021300906052B0E03021A0500041443D232A77578792F67D9469EBEE29E\
  284. B6D88FF59AA082037D3082037930820265A00302010202109BCC3B23A3549F9645D0A425322CF7FD\
  285. 300906052B0E03021D05003040310B3009060355040613025553311B3019060355040A131253716C\
  286. 5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C522043657274302017\
  287. 0D3137303831383033313030345A180F32303939313233313034303030305A3040310B3009060355\
  288. 040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D3114301206\
  289. 03550403130B53514C434C52204365727430820122300D06092A864886F70D01010105000382010F\
  290. 003082010A0282010100CCEA3F8D614CE7AB2B52E39301A9D9E2A7828473EC10D8748CF0BB32DDF6\
  291. ED1423E56C0FC36DAA995D54BD7194BF3BC71B1931111706DF162ED5188017B0B570DDF9AFFD3E53\
  292. 1E266BFADD0222025353277BAC965E89DB4645438A973256897E0D2644C24CB3C61AC0011FC7CDDF\
  293. E06C1234C1C0A601586952FF2033C58F8A519AD1D71B35A6975E1F846A83D1F90D1602A372CC8EB6\
  294. 97240E9E38852BBB96CB78FE296B57DE401AAFD958EDDF59894E8C0A42B95A77BE43C1B893CE3128\
  295. 24B68C931F9893639A41BF99E68AD6F2AA2AEC58AA5CC067EF8FF0AD7BE9A1AA44B48F8338BBD324\
  296. C2D162DA30356FD461BF2FD388EDDFDD5990682379EF18A33C4D0203010001A37530733071060355\
  297. 1D01046A30688010B7C292D45B0F3FDDBF55241F2FD6F2CFA1423040310B30090603550406130255\
  298. 53311B3019060355040A131253716C5175616E74756D4C6561702E636F6D31143012060355040313\
  299. 0B53514C434C52204365727482109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D05\
  300. 0003820101006AF2D8F382D55034DE7715B0F5536669914C6B256344B7343B0B099B32B4759E77B3\
  301. 67CA19A3B3FBC7DBCF913FB022DD5D4F83EB6792D58B63AF015767F0BC059D24D3E019880A95DEA9\
  302. E72BD462302806CF9BFB1C9A3E3BAC4D612889ACF4839AC0FE7F60625FA33DABA6F0FCD6EBF88401\
  303. 712EF3DA505092CBB5ACCDC510258F3003868DC15C0E5374B01E6BF555972E3F99C789F1E14B171C\
  304. E508D895CDD71AB6FD8D9F61962A3E1D89E0E748E3C5505AF21E0DF6D95FEA86E87D5DED7D00A669\
  305. FC8CAB472A93F993BBB21636AEDE56F747E11FCF69F96D4C87732C5BE3780E01C772F3BD0938DD5A\
  306. 440BDCC846808395728AF10C95C6F8E6714DDAFA19B2318201CF308201CB02010130543040310B30\
  307. 09060355040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D31\
  308. 1430120603550403130B53514C434C52204365727402109BCC3B23A3549F9645D0A425322CF7FD30\
  309. 0906052B0E03021A0500A0523010060A2B06010401823702010C31023000301906092A864886F70D\
  310. 010903310C060A2B060104018237020104302306092A864886F70D0109043116041470E1D5141ADB\
  311. 71E29F00A5D80E2A58B2BCBA01D4300D06092A864886F70D010101050004820100AF02DE1136A6CE\
  312. 693A4C46F9DBB494FD7FE687F0C014A75B5A8DEB0D6B7A0CB967C42A09B743E0124891B3598FADAB\
  313. A6DF6812378F15A0B9C7B8AB076A90D320BAFC220F5438C4204E2A5A770DDD93AB2E4AD324198659\
  314. 9C991196FBC115ACFBB5B492B21F2FB6DCB89D4B61E2E19C716693C6A2F9DE12572E259CBFD48238\
  315. 003FF893D95D3EC5C025CD9323147A0D4BEAB0F2C430C9F1C0B035FA983FF62E7F840723B9B7DABC\
  316. D580FC3347C1173DFDF56A94EC5498FFB11D0F6816A3E0432E986327CC6A2CC9858CDD79C39E6C8F\
  317. 929CCD8DE79B7C0C6EC77556E7EFAF79E2A2B79CE11AE5F4A9EC459E1C290E7935CAB66AD1B957FE\
  318. 8D4829365E24549A9F00000000000000
  319.         WITH PERMISSION_SET = SAFE;
  320. END;
  321.  
  322.  
  323. IF (OBJECT_ID(N'dbo.String_PadLeft') IS NULL)
  324. BEGIN
  325.     PRINT N'Creating [dbo].[String_PadLeft]...';
  326.     EXEC(N'
  327. CREATE FUNCTION [dbo].[String_PadLeft]
  328. (
  329.  @InputString NVARCHAR(4000),
  330.  @PadChar NVARCHAR(1),
  331.  @PadAmount INT
  332. )
  333. RETURNS NVARCHAR(4000)
  334. WITH RETURNS NULL ON NULL INPUT
  335. AS EXTERNAL NAME [SQL2017_ClrStrictSecurity2].[UserDefinedFunctions].[String_PadLeft];
  336. ');
  337. END;
  338. GO
  339.  
  340.  
  341. PRINT 'Running three tests...';
  342. SELECT dbo.String_PadLeft(N'3456', N'~', 7); -- ~~~3456
  343. SELECT dbo.String_PadLeft(N'34567', N'+', 3); -- 34567
  344. SELECT dbo.String_PadLeft(NULL, N'f', 5); -- NULL
  345.  
  346.  
  347. PRINT 'Removing UNSAFE ASSEMBLY permission...';
  348. EXEC (N'
  349. USE [master];
  350.  
  351. REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity2-Login];
  352. ');
  353.  
  354.  
  355. PRINT 'Re-running first test (it should error in SQL Server 2017)...';
  356. SELECT dbo.String_PadLeft(N'3456', N'~', 7); -- ~~~3456
  357.  
  358. /* RETURNS:
  359.  
  360. ~~~3456
  361. 34567
  362. NULL
  363.  
  364. */
  365. GO
  366.  
  367. SELECT ASSEMBLYPROPERTY(N'sql2017_clrstrictsecurity2', 'CLRName') AS [CLRName];
  368. -- sql2017_clrstrictsecurity2, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top