1:45 PM 11/12/2025<!-- GIF89;a -->
<!-- GIF89;a -->
ï¿½ï¿½ï¿½ï¿½ JFIF      ï¿½ï¿½ ï¿½ 	  	 

	



 "" $(4,$&1'-=-157:::#+?D?8C49:7




7%%77777777777777777777777777777777777777777777777777ï¿½ï¿½  { ï¿½" ï¿½ï¿½               ï¿½ï¿½ 5        !1AQa"qï¿½2ï¿½ï¿½BRï¿½ï¿½#bï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½                ï¿½ï¿½                 ï¿½ï¿½   ? ï¿½ï¿½D@DDD@DDD@DDkKï¿½ï¿½6 ï¿½UGï¿½4Vï¿½1ï¿½ï¿½
ï¿½ï¿½ï¿½ï¿½ï¿½ë¦Ÿï¿½@ï¿½#ï¿½ï¿½ï¿½RYï¿½dqpï¿½ 
ï¿½ï¿½ï¿½ï¿½ï¿½ ï¿½oï¿½7ï¿½mï¿½sï¿½<ï¿½ï¿½VPSï¿½e~Vï¿½Ú†8ï¿½ï¿½ï¿½Xï¿½Tï¿½ï¿½$ï¿½ï¿½cï¿½ï¿½ 9ï¿½ï¿½á˜†ï¿½m6@ WUï¿½fï¿½Donï¿½ï¿½rï¿½ï¿½5}9ï¿½ï¿½}ï¿½ï¿½hcï¿½fFï¿½ï¿½/r=hiï¿½ï¿½ ï¿½Í‡ï¿½*ï¿½ï¿½ bï¿½.ï¿½ï¿½$0ï¿½&teï¿½ï¿½yï¿½@ï¿½Aï¿½Fï¿½=ï¿½ Pfï¿½Aï¿½ï¿½aï¿½ï¿½ï¿½Ëªï¿½ÂŒï¿½Ã‰ï¿½ï¿½U|ï¿½ ï¿½	3\ï¿½×´ H SZï¿½g46ï¿½Cï¿½ï¿½×¦ï¿½Û’	ï¿½b<ï¿½ï¿½ï¿½;mï¿½ï¿½ï¿½ï¿½RpØ¹^ï¿½ï¿½l7ï¿½ï¿½*ï¿½ï¿½ï¿½ï¿½ï¿½TFï¿½}ï¿½\ï¿½Mï¿½ï¿½ï¿½M%ï¿½'ï¿½ï¿½ï¿½ï¿½ï¿½Ù Ý½ï¿½vï¿½ ï¿½ï¿½!-ï¿½ï¿½ï¿½ï¿½ï¿½?ï¿½N!Laï¿½ï¿½A+[`#ï¿½ï¿½ï¿½Mï¿½ï¿½ï¿½ï¿½'ï¿½~oRï¿½?ï¿½ï¿½v^)ï¿½ï¿½=ï¿½ï¿½hï¿½ï¿½ï¿½ï¿½Aï¿½ï¿½Xï¿½.ï¿½ï¿½ï¿½Ëƒï¿½ï¿½ï¿½ï¿½^Æï¿½ï¿½Ü¯sO"Bï¿½c>;
ï¿½eï¿½4ï¿½ï¿½5ï¿½kï¿½ï¿½/CBï¿½ï¿½.
 ï¿½J?ï¿½ï¿½;ï¿½Òˆï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½~ï¿½<ï¿½VZï¿½ê­¼2/)Í”jCï¿½ï¿½ï¿½×¢ï¿½Vï¿½Gï¿½!ï¿½ï¿½ï¿½!ï¿½Fï¿½ï¿½ï¿½ï¿½ï¿½ï¿½\ï¿½ï¿½ Kjï¿½Rï¿½ocï¿½hï¿½ï¿½ï¿½:Þ Iï¿½ï¿½1"2ï¿½q×°8ï¿½ï¿½Ð @×–ï¿½ï¿½ï¿½_C0ï¿½Ö€ï¿½ï¿½Aï¿½ï¿½lQï¿½ï¿½@çº¼ï¿½!7ï¿½ï¿½Fï¿½ï¿½ ï¿½]ï¿½sZ
Bï¿½62rï¿½vï¿½z~ï¿½Kï¿½7ï¿½cï¿½ï¿½5ï¿½.ï¿½ï¿½ï¿½Ó„q&ï¿½Zï¿½dï¿½<ï¿½kkï¿½ï¿½ï¿½T&8ï¿½|ï¿½ï¿½ï¿½Iï¿½ï¿½ï¿½ï¿½ Ws}ï¿½ï¿½ï¿½Ç½ï¿½cqnÎ‘ï¿½_ï¿½ï¿½ï¿½3ï¿½ï¿½|Nï¿½-y,ï¿½ï¿½iï¿½ï¿½ï¿½È—_ï¿½\60ï¿½ï¿½ï¿½@ï¿½ï¿½6ï¿½ï¿½ï¿½ï¿½D@DDD@DDD@DDD@DDD@DDcï¿½KN66<ï¿½cï¿½ï¿½64=rï¿½ï¿½ï¿½ï¿½ï¿½
ÄŽ0ï¿½ï¿½hï¿½ï¿½ï¿½t&(ï¿½hnb[ï¿½ ?ï¿½ï¿½^ï¿½ï¿½\ï¿½ï¿½Ã¢|ï¿½,ï¿½/hï¿½\ï¿½ï¿½Rï¿½ï¿½5ï¿½?
ï¿½0ï¿½!×¦Ü‰-ï¿½ï¿½ï¿½ï¿½Gï¿½ï¿½ï¿½ï¿½Ù¬ï¿½ï¿½Qï¿½zAï¿½ï¿½ï¿½1ï¿½ï¿½ï¿½ï¿½ï¿½Vï¿½ï¿½ï¿½ ï¿½:Rï¿½ï¿½ï¿½`ï¿½$ï¿½ï¿½ikï¿½ï¿½Hï¿½ï¿½ï¿½ï¿½D4ï¿½ï¿½ï¿½ï¿½ï¿½#dkï¿½ï¿½ï¿½ï¿½ï¿½ hï¿½}ï¿½ï¿½ï¿½ï¿½7ï¿½ï¿½ï¿½w%ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½*o8wGï¿½LycuTï¿½.ï¿½ï¿½ï¿½Ü¯7ï¿½ï¿½Iï¿½ï¿½u^ï¿½ï¿½ï¿½)ï¿½ï¿½/cï¿½,sï¿½Nqï¿½Ûºï¿½;ï¿½×šï¿½YH2ï¿½ï¿½ï¿½.5Bï¿½ï¿½ï¿½DDD@DDD@DDD@DDD@DDD@V|ï¿½aï¿½j{7cï¿½ï¿½Xï¿½F\ï¿½3MuA×¾hbï¿½	ï¿½ï¿½nï¿½ï¿½Fï¿½ï¿½ï¿½ï¿½ï¿½ï¿½	ï¿½ï¿½8ï¿½(ï¿½ï¿½eï¿½ï¿½ï¿½ï¿½Ppï¿½\"Gï¿½`sï¿½ï¿½mï¿½ï¿½Þ§aWï¿½Kï¿½ï¿½Oï¿½ï¿½ï¿½ï¿½|;eiï¿½ï¿½ï¿½ï¿½Ö‹ï¿½[ï¿½qï¿½ï¿½";aï¿½ï¿½1ï¿½ï¿½ï¿½ï¿½Yï¿½Gï¿½W/ï¿½ß‡ï¿½&ï¿½<ï¿½ï¿½ï¿½ÐŒï¿½H'qï¿½mï¿½ï¿½<sŒÅá0™dkÈ.tc˜:z­G†:<FV2Zu“V

N(ëá’b&1K
¼Àë_Û{®®×ñ5ÇÁ(HæŒíh¡£{è.×€ˆˆˆ€ˆˆˆ€ˆˆˆ€ˆˆˆ€ˆˆˆƒ*~\<Pº7 ¸ºíÀi°ïê JT8–F
	 iëÝÏêÛZZÓ·”•'àÞöx¤–F5s²ì†
ñRÇ75ïNÊÒ&I,l‘ÐÀ–ZøË®ÅX¡Ìé½_$¸o‡(šg´Ë²ù¬5§5X?Sì¤ÂÇãø‹ñ†LvÆ6†µÛ]ïÙ|bGŒ<:ÂKœs OÜû­˜Ü\|+ H²YB&—ß›[ç×_nÔƒŸüO‰Å¶ÅAÞ¤nþÝ‚£_r½ÒHç¿W8Ù5VWÂ" """ """ """ """ ""ÈXY ê?Ë!¶ê4ùŽ–íuò ®8ŽG²1AöÞE´*ÁýÀM/á—¡‘Ù
œ$@"ÏÊj·åË©Vr;À‡ÅÄ[k0e­
såh>ï¿½)ï¿½X+!ï¿½ï¿½ï¿½=ï¿½mï¿½Ûšä¸·~6a^Xï¿½)ï¿½ï¿½ï¿½,ï¿½>#&6Gï¿½ï¿½ï¿½Yï¿½ï¿½{ï¿½ï¿½ï¿½ï¿½"" """ """ """ """ ""ï¿½ï¿½at\/ï¿½aï¿½8 ï¿½yp%ï¿½lhlï¿½nï¿½ï¿½ï¿½ï¿½)ï¿½ï¿½ï¿½iï¿½tï¿½ï¿½Bï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½?ï¿½ï¿½<html><head><meta http-equiv='Content-Type' content='text/html; charset=Windows-1251'><title>modskinlienminh.com - WSOX ENC</title>

‰PNG

   
IHDR   Ÿ   f   Õ†C1   sRGB ®Îé   gAMA  ±üa   	pHYs  Ã  ÃÇo¨d  GIDATx^íÜL”÷ð÷Yçªö("Bh_ò«®¸¢§q5kÖ*:þ0A­ºšÖ¥]VkJ¢M»¶f¸±8\k2íll£1]q®ÙÔ‚ÆT

h25jguaT5*！﻿‰PNG

   
IHDR   Ÿ   f   Õ†C1   sRGB ®Îé   gAMA  ±üa   	pHYs  Ã  ÃÇo¨d  GIDATx^íÜL”÷ð÷Yçªö("Bh_ò«®¸¢§q5kÖ*:þ0A­ºšÖ¥]VkJ¢M»¶f¸±8\k2íll£1]q®ÙÔ‚ÆT

h25jguaT5*！<br />
<b>Warning</b>:  Undefined variable $authorization in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>57</b><br />
<br />
<b>Warning</b>:  Undefined variable $translation in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>118</b><br />
<br />
<b>Warning</b>:  Trying to access array offset on value of type null in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>119</b><br />
<br />
<b>Warning</b>:  file_get_contents(https://raw.githubusercontent.com/Den1xxx/Filemanager/master/languages/ru.json): Failed to open stream: HTTP request failed! HTTP/1.1 404 Not Found
 in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>120</b><br />
<br />
<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\demo\fi.php:1) in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>247</b><br />
<br />
<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\demo\fi.php:1) in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>248</b><br />
<br />
<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\demo\fi.php:1) in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>249</b><br />
<br />
<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\demo\fi.php:1) in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>250</b><br />
<br />
<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\demo\fi.php:1) in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>251</b><br />
<br />
<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\demo\fi.php:1) in <b>C:\xampp\htdocs\demo\fi.php</b> on line <b>252</b><br />
MZ�       ��  �       @                                   �   � �	�!�L�!This program cannot be run in DOS mode.
$       ��<߱�R���R���R�U�����R�U�P���R�Rich��R�                PE  L             � !     <                        
   
           `    ,+  @                                       9                            8                                                                           .rdata  �                        @  @.rsrc   9      :                @  @    ./�6       T   8  8      ./�6       $   �  �         8   .rdata  8  x   .rdata$zzzdbg       @  .rsrc$01    @"  �6 .rsrc$02        ��� ����#�jTK�����v�V%5�./�6                                                                                                                                                                                                                                                                                                                                                               �8  �4 �`  � �x  �$ ��  �   �  �               f   �  �g   �  �h   �  �                   �               e   ( �u   @ �                  X �                  p �               	  �                 	  �                 	  �                 	  �                 	  �                 	  �                 	  �                 	  �  �W  z          ��  �         S �          X �           �%  �/          �U  =           X            @"  |           R E G I S T R Y  F I L E  T Y P E L I B  M U I     l4   V S _ V E R S I O N _ I N F O     ���     
  |O  
  |O?                        �   S t r i n g F i l e I n f o   �   0 4 0 9 0 4 B 0   L   C o m p a n y N a m e     M i c r o s o f t   C o r p o r a t i o n   F   F i l e D e s c r i p t i o n     a z r o l e s   M o d u l e     h $  F i l e V e r s i o n     1 0 . 0 . 2 0 3 4 8 . 1   ( W i n B u i l d . 1 6 0 1 0 1 . 0 8 0 0 )   0   I n t e r n a l N a m e   a z r o l e s   � .  L e g a l C o p y r i g h t   �   M i c r o s o f t   C o r p o r a t i o n .   A l l   r i g h t s   r e s e r v e d .   8   O r i g i n a l F i l e n a m e   a z r o l e s   j %  P r o d u c t N a m e     M i c r o s o f t �   W i n d o w s �   O p e r a t i n g   S y s t e m     >   P r o d u c t V e r s i o n   1 0 . 0 . 2 0 3 4 8 . 1     D    V a r F i l e I n f o     $    T r a n s l a t i o n     	�                    HKCR
{
	AzRoles.AzAuthorizationStore.1 = s 'AzAuthorizationStore Class'
	{
		CLSID = s '{b2bcff59-a757-4b0b-a1bc-ea69981da69e}'
	}
	AzRoles.AzAuthorizationStore = s 'AzAuthorizationStore Class'
	{
		CurVer = s 'AzRoles.AzAuthorizationStore.1'
	}
	NoRemove CLSID
	{
		ForceRemove {b2bcff59-a757-4b0b-a1bc-ea69981da69e} = s 'AzAuthorizationStore Class'
		{
			ProgID = s 'AzRoles.AzAuthorizationStore.1'
			VersionIndependentProgID = s 'AzRoles.AzAuthorizationStore'
			ForceRemove 'Programmable'
			InprocServer32 = s '%MODULE%'
			{
				val ThreadingModel = s 'Both'
			}
		}
	}
	
	AzRoles.AzPrincipalLocator.1 = s 'AzPrincipalLocator Class'
	{
		CLSID = s '{483afb5d-70df-4e16-abdc-a1de4d015a3e}'
	}
	AzRoles.AzPrincipalLocator = s 'AzPrincipalLocator Class'
	{
		CurVer = s 'AzRoles.AzPrincipalLocator.1'
	}
	NoRemove CLSID
	{
		ForceRemove {483afb5d-70df-4e16-abdc-a1de4d015a3e} = s 'AzPrincipalLocator Class'
		{
			ProgID = s 'AzRoles.AzPrincipalLocator.1'
			VersionIndependentProgID = s 'AzRoles.AzPrincipalLocator'
			ForceRemove 'Programmable'
			InprocServer32 = s '%MODULE%'
			{
				val ThreadingModel = s 'Both'
			}
		}
	}
	NoRemove Interface
	{
	    ForceRemove {edbd9ca9-9b82-4f6a-9e8b-98301e450f14} = s 'IAzAuthorizationStore interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {b11e5584-d577-4273-b6c5-0973e0f8e80d} = s 'IAzAuthorizationStore2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {abc08425-0c86-4fa0-9be3-7189956c926e} = s 'IAzAuthorizationStore3 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {987bc7c7-b813-4d27-bede-6ba5ae867e95} = s 'IAzApplication interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {086a68af-a249-437c-b18d-d4d86d6a9660} = s 'IAzApplication2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {181c845e-7196-4a7d-ac2e-020c0bb7a303} = s 'IAzApplication3 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {929b11a9-95c5-4a84-a29a-20ad42c2f16c} = s 'IAzApplications interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {5e56b24f-ea01-4d61-be44-c49b5e4eaf74} = s 'IAzOperation interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {1f5ea01f-44a2-4184-9c48-a75b4dcc8ccc} = s 'IAzOperation2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {90ef9c07-9706-49d9-af80-0438a5f3ec35} = s 'IAzOperations interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {cb94e592-2e0e-4a6c-a336-b89a6dc1e388} = s 'IAzTask interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {03a9a5ee-48c8-4832-9025-aad503c46526} = s 'IAzTask2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {b338ccab-4c85-4388-8c0a-c58592bad398} = s 'IAzTasks interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {00e52487-e08d-4514-b62e-877d5645f5ab} = s 'IAzScope interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {ee9fe8c9-c9f3-40e2-aa12-d1d8599727fd} = s 'IAzScope2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {78e14853-9f5e-406d-9b91-6bdba6973510} = s 'IAzScopes interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {f1b744cd-58a6-4e06-9fbf-36f6d779e21e} = s 'IAzApplicationGroup interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {3f0613fc-b71a-464e-a11d-5b881a56cefa} = s 'IAzApplicationGroup2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {4ce66ad5-9f3c-469d-a911-b99887a7e685} = s 'IAzApplicationGroups interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {859e0d8d-62d7-41d8-a034-c0cd5d43fdfa} = s 'IAzRole interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {95e0f119-13b4-4dae-b65f-2f7d60d822e4} = s 'IAzRoles interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {eff1f00b-488a-466d-afd9-a401c5f9eef5} = s 'IAzClientContext interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {2b0c92b8-208a-488a-8f81-e4edb22111cd} = s 'IAzClientContext2 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {11894fde-1deb-4b4b-8907-6d1cda1f5d4f} = s 'IAzClientContext3 interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {e192f17d-d59f-455e-a152-940316cd77b2} = s 'IAzBizRuleContext interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {fc17685f-e25d-4dcd-bae1-276ec9533cb5} = s 'IAzBizRuleParameters interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {e94128c7-e9da-44cc-b0bd-53036f3aab3d} = s 'IAzBizRuleInterfaces interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {d97fcea1-2599-44f1-9fc3-58e9fbe09466} = s 'IAzRoleDefinition interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {881f25a5-d755-4550-957a-d503a3b34001} = s 'IAzRoleDefinitions interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {55647d31-0d5a-4fa3-b4ac-2b5f9ad5ab76} = s 'IAzRoleAssignment interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {9c80b900-fceb-4d73-a0f4-c83b0bbf2481} = s 'IAzRoleAssignments interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {e5c3507d-ad6a-4992-9c7f-74ab480b44cc} = s 'IAzPrincipalLocator interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {504d0f15-73e2-43df-a870-a64f40714f53} = s 'IAzNameResolver interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	    ForceRemove {63130a48-699a-42d8-bf01-c62ac3fb79f9} = s 'IAzObjectPicker interface'
		{
			ProxyStubClsid = s '{00020424-0000-0000-C000-000000000046}'
			ProxyStubClsid32 = s '{00020424-0000-0000-C000-000000000046}'
			TypeLib = s '{11A8B8EE-BF30-409A-8EF7-3A143EF70332}'
			{
				val Version = s '1.0'
			}
		}
	}
}
       HKCR
{
	AzRoles.AzBizRuleContext.1 = s 'AzBizRuleContext Class'
	{
		CLSID = s '{5c2dc96f-8d51-434b-b33c-379bccae77c3}'
	}
	AzRoles.AzBizRuleContext = s 'AzBizRuleContext Class'
	{
		CurVer = s 'AzRoles.AzBizRuleContext.1'
	}
	NoRemove CLSID
	{
		ForceRemove {5c2dc96f-8d51-434b-b33c-379bccae77c3} = s 'AzBizRuleContext Class'
		{
			ProgID = s 'AzRoles.AzBizRuleContext.1'
			VersionIndependentProgID = s 'AzRoles.AzBizRuleContext'
			ForceRemove 'Programmable'
			InprocServer32 = s '%MODULE%'
			{
				val ThreadingModel = s 'Both'
			}
		}
	}
}
   CREATE TABLE [dbo].[AzMan_AzApplicationGroup] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ObjectGuid] [uniqueidentifier] NULL,       
    [StoreID]       [int] , 
    [AppID]         [int] ,     
    [ScopeID]       [int] ,         
    [ParentType] [tinyint] NOT NULL ,   
    [Name] [nvarchar] (64) ,
    [Description] [nvarchar] (1024) ,
    [ApplicationData] [ntext]  ,
    [GroupType] [tinyint] NULL,
    [LdapQueryID]   [int] NULL,
    [RowUpdateTimeStamp] [timestamp] NULL , 
    [ChildUpdateTimeStamp] [binary] (8) NULL  
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[AzMan_AzAuthorizationStore] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ObjectGuid] [uniqueidentifier] NULL,   
    [Description] [nvarchar] (1024) ,
    [Name] [nvarchar] (512) ,   
    [ApplicationData] [ntext]  ,
    [DomainTimeout] [int] NULL ,
    [ScriptEngineTimeout] [int] NULL ,
    [MaxScriptEngines] [int] NULL ,
    [TargetMachine] [nvarchar] (50) ,
    [ApplyStoreSacl] [bit] NULL ,
    [GenerateAudits] [bit] NULL ,
    [MajorVersion] [int] NULL,
    [MinorVersion] [int] NULL,
    [RowUpdateTimeStamp] [timestamp] NULL ,     
    [ChildUpdateTimeStamp] [binary] (8) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_AzApplication] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ObjectGuid] [uniqueidentifier] NULL,   
    [StoreID] [int] NOT NULL ,
    [Name] [nvarchar] (512) ,
    [Description] [nvarchar] (1024) ,
    [ApplicationData] [ntext]  ,
    [ApplyStoreSacl] [bit] NULL ,
    [GenerateAudits] [bit] NULL ,
    [AuthzInterfaceClsId] [int] NULL ,
    [ApplicationVersion] [nvarchar] (50),
    [RowUpdateTimeStamp] [timestamp] NULL ,     
    [ChildUpdateTimeStamp] [binary] (8) NULL  
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[AzMan_BizRule_To_Task] (
    [BizRuleID] [int]   NOT NULL ,
    [TaskID]    [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_BizRule_To_Group] (
    [BizRuleID] [int]   NOT NULL ,
    [GroupID]   [int]   NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_BizRule] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ParentId] [int] NOT NULL ,
    [ParentType] [tinyint] not NULL,
    [BizRuleImportedPath] [nvarchar] (512) ,
    [BizRule] [ntext] ,
    [BizRuleLanguage] [nvarchar] (64), 
    [RowUpdateTimeStamp] [timestamp] NULL           
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_LDAPQuery] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [GroupID] [int] NOT NULL ,
    [LdapQuery] [ntext] ,
    [RowUpdateTimeStamp] [timestamp] NULL           
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_Group_SIDMember] (
    [GroupID] [int] NOT NULL ,
    [MemberSID] [varbinary] (85) NOT NULL ,
    [Member] [bit] Not NULL ,
    [RowUpdateTimeStamp] [timestamp] NULL 
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[AzMan_Group_AppMember] (
    [GroupID]       [int] NOT NULL ,
    [ChildID]       [int] NOT NULL ,
    [Member]        [bit] Not NULL ,
    [RowUpdateTimeStamp] [timestamp] NULL       
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_AzOperation] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ObjectGuid] [uniqueidentifier] NULL,   
    [AppID] [int] NOT NULL ,
    [Name] [nvarchar] (64) ,
    [Description] [nvarchar] (1024) ,
    [ApplicationData] [ntext]  ,
    [OperationID] [int] NULL,
    [RowUpdateTimeStamp] [timestamp] NULL ,         
    [ChildUpdateTimeStamp] [binary] (8) NULL  
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_AzTask] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ObjectGuid] [uniqueidentifier] NULL,   
    [AppID] [int],
    [ScopeID] [int],
    [ParentType] [tinyint] NOT NULL ,       
    [Name] [nvarchar] (64) ,
    [Description] [nvarchar] (1024) ,
    [ApplicationData] [ntext]  ,
    [IsRoleDefinition] [bit] NULL ,
    [RowUpdateTimeStamp] [timestamp] NULL ,     
    [ChildUpdateTimeStamp] [binary] (8) NULL  
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_AzScope] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [AppID] [int] NOT NULL ,
    [Name] [ntext],
    [NameLen]   [int]  NOT NULL,
    [NameHash]  [Binary] (32) NULL,
    [Description] [nvarchar] (1024) ,
    [ApplicationData] [ntext]  ,
    [ObjectGuid] [uniqueidentifier] NULL,
    [RowUpdateTimeStamp] [timestamp] NULL ,     
    [ChildUpdateTimeStamp] [binary] (8) NULL,
    [HasSpecificUsers] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_AzRoleAssignment] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [AppID] [int],
    [ScopeID] [int],
    [ParentType] [tinyint] NOT NULL ,       
    [Name] [nvarchar] (64) ,
    [Description] [nvarchar] (1024) ,
    [ApplicationData] [ntext]  ,
    [ObjectGuid] [uniqueidentifier] NULL ,
    [RowUpdateTimeStamp] [timestamp] NULL ,     
    [ChildUpdateTimeStamp] [binary] (8) NULL  
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_Role_SIDMember] (
    [RoleID] [int] NOT NULL ,
    [MemberSID] [varbinary] (85) NOT NULL,
    [RowUpdateTimeStamp] [timestamp] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_Role_AppMember] (
    [RoleID]        [int] NOT NULL ,
    [ChildID]       [int] NOT NULL ,
    [RowUpdateTimeStamp] [timestamp] NULL       
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_Role_To_Task_Link] (
    [TaskID]  [int] NOT NULL ,
    [RoleID]  [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_Role_To_Operation_Link] (
    [OperationID]  [int]    NOT NULL ,
    [RoleID]        [int]   NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AzMan_Task_To_Task_Link] (
    [TaskID]  [int] NOT NULL ,
    [ChildID]   [int]   NOT NULL 
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Azman_SQLRole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Azman_SQLRole]
GO

CREATE TABLE [dbo].[Azman_SQLRole] (
    [uid]      [int] not null,
    [ObjectID] [int] NULL ,
    [ObjectType] [tinyint] NULL ,
    [SQLRoleName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,   
    [RoleType] [tinyint] NULL 
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[AzMan_Task_To_Operation_Link] (
    [OperationID]   [int]   NOT NULL ,
    [TaskID]        [int]   NOT NULL 
) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzApplication_StoreID] ON [dbo].[AzMan_AzApplication]([StoreID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzApplicationGroup_AppID] ON [dbo].[AzMan_AzApplicationGroup]([AppID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzApplicationGroup_ScopeID] ON [dbo].[AzMan_AzApplicationGroup]([ScopeID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzApplicationGroup_Name] ON [dbo].[AzMan_AzApplicationGroup]([Name]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_AzOperation_Name] ON [dbo].[AzMan_AzOperation]([Name]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_AzOperation_AppID] ON [dbo].[AzMan_AzOperation]([AppID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzRoleAssignment_AppID] ON [dbo].[AzMan_AzRoleAssignment]([AppID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzRoleAssignment_ScopeID] ON [dbo].[AzMan_AzRoleAssignment]([ScopeID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_AzRoleAssignment_Name] ON [dbo].[AzMan_AzRoleAssignment]([Name]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_AzScope_AppID] ON [dbo].[AzMan_AzScope]([AppID]) ON [PRIMARY]
GO
  

CREATE  INDEX [IX_Azman_Scope_Name] ON [dbo].[AzMan_AzScope] ([AppID], [NameHash]) ON [PRIMARY]  
Go  

CREATE  INDEX [IX_AzMan_AzTask_AppID] ON [dbo].[AzMan_AzTask]([AppID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_AzMan_AzTask_ScopeID] ON [dbo].[AzMan_AzTask]([ScopeID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_AzTask_Name] ON [dbo].[AzMan_AzTask]([Name]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_BizRule_Parent] ON [dbo].[AzMan_BizRule]([ParentId], [ParentType]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_Group_AppMember_GroupID] ON [dbo].[AzMan_Group_AppMember]([GroupID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AzMan_LDAPQuery_GroupID] ON [dbo].[AzMan_LDAPQuery]([GroupID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_AzApplication] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzApplication] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_AzAuthorizationStore] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzAuthorizationStore] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_AzScope] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzScope] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO


ALTER TABLE [dbo].[AzMan_AzApplicationGroup] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzApplicationGroup] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_AzMan_AzAppGroup_AzMan_AzApplication] FOREIGN KEY 
    (
        [AppID]
    ) REFERENCES [dbo].[AzMan_AzApplication] (
        [ID]
    ) ,
    CONSTRAINT [FK_AzMan_AzAppGroup_AzMan_AzAuthorizationStore] FOREIGN KEY 
    (
        [StoreID]
    ) REFERENCES [dbo].[AzMan_AzAuthorizationStore] (
        [ID]
    ) ,
    CONSTRAINT [FK_AzMan_AzAppGroup_AzMan_AzScope] FOREIGN KEY 
    (
        [ScopeID]
    ) REFERENCES [dbo].[AzMan_AzScope] (
        [ID]
    ) 
    
GO

ALTER TABLE [dbo].[AzMan_AzApplicationGroup] WITH NOCHECK ADD 
    CONSTRAINT [CK_AzMan_AzApplicationGroup] CHECK (sign(coalesce([StoreID],0)) + sign(coalesce([AppID],0)) + sign(coalesce([ScopeID],0)) = 1)
GO

ALTER TABLE [dbo].[AzMan_AzRoleAssignment] WITH NOCHECK ADD 
    CONSTRAINT [CK_AzMan_AzRoleAssignment] CHECK (sign(coalesce([AppID],0)) + sign(coalesce([ScopeID],0)) = 1)
GO

ALTER TABLE [dbo].[AzMan_AzTask] WITH NOCHECK ADD 
    CONSTRAINT [CK_AzMan_AzTask] CHECK (sign(coalesce([AppID],0)) + sign(coalesce([ScopeID],0)) = 1)
GO

alter table [dbo].[AzMan_AzApplicationGroup] nocheck constraint [FK_AzMan_AzAppGroup_AzMan_AzApplication]
GO

alter table [dbo].[AzMan_AzApplicationGroup] nocheck constraint [FK_AzMan_AzAppGroup_AzMan_AzAuthorizationStore]
GO

alter table [dbo].[AzMan_AzApplicationGroup] nocheck constraint [FK_AzMan_AzAppGroup_AzMan_AzScope]
GO


ALTER TABLE [dbo].[AzMan_AzOperation] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzOperation] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_AzRoleAssignment] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzRoleAssignment] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] ,
    
    CONSTRAINT [FK_AzMan_Role_To_App] FOREIGN KEY 
    (
        [AppID]
    ) REFERENCES [dbo].[AzMan_AzApplication] (
        [ID]
    ) ,

    CONSTRAINT [FK_AzMan_Role_To_Scope] FOREIGN KEY 
    (
        [ScopeID]
    ) REFERENCES [dbo].[AzMan_AzScope] (
        [ID]
    ) 
    
GO

alter table [dbo].[AzMan_AzRoleAssignment] nocheck constraint [FK_AzMan_Role_To_App]
GO

alter table [dbo].[AzMan_AzRoleAssignment] nocheck constraint FK_AzMan_Role_To_Scope
GO


ALTER TABLE [dbo].[AzMan_AzTask] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_AzTask] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_AzMan_AzTask_To_App] FOREIGN KEY 
    (
        [AppID]
    ) REFERENCES [dbo].[AzMan_AzApplication] (
        [ID]
    ) ,
    CONSTRAINT [FK_AzMan_AzTask_To_Scope] FOREIGN KEY 
    (
        [ScopeID]
    ) REFERENCES [dbo].[AzMan_AzScope] (
        [ID]
    )   
GO

alter table [dbo].[AzMan_AzTask] nocheck constraint [FK_AzMan_AzTask_To_App]
GO

alter table [dbo].[AzMan_AzTask] nocheck constraint [FK_AzMan_AzTask_To_Scope]
GO

ALTER TABLE [dbo].[AzMan_BizRule] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_BizRule] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_LDAPQuery] WITH NOCHECK ADD 
    CONSTRAINT [PK_AzMan_LDAPQuery] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_AzOperation] WITH NOCHECK ADD 
    CONSTRAINT [IX_AzMan_AzOperation_OpID] UNIQUE  NONCLUSTERED 
    (
        [OperationID],
        [AppID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_Group_AppMember] WITH NOCHECK ADD 
    CONSTRAINT [IX_AzMan_Group_AppMember_member] UNIQUE  NONCLUSTERED 
    (
        [GroupID],
        [ChildID],
        [Member]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_Group_SIDMember] WITH NOCHECK ADD 
    CONSTRAINT [IX_AzMan_Group_SIDMember_member] UNIQUE  NONCLUSTERED 
    (
        [GroupID],
        [MemberSID],
        [Member]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_Role_AppMember] WITH NOCHECK ADD 
    CONSTRAINT [IX_AzMan_Role_AppMember_member] UNIQUE  NONCLUSTERED 
    (
        [RoleID],
        [ChildID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_Role_SIDMember] WITH NOCHECK ADD 
    CONSTRAINT [IX_AzMan_Role_SIDMember_member] UNIQUE  NONCLUSTERED 
    (
        [RoleID],
        [MemberSID]
    )  ON [PRIMARY] 
GO


ALTER TABLE [dbo].[AzMan_AzApplication] ADD 
    CONSTRAINT [FK_AzMan_AzApplication_AzMan_AzAuthorizationStore] FOREIGN KEY 
    (
        [StoreID]
    ) REFERENCES [dbo].[AzMan_AzAuthorizationStore] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
GO


ALTER TABLE [dbo].[AzMan_AzOperation] ADD 
    CONSTRAINT [FK_AzMan_AzOperation_AzMan_AzApplication] FOREIGN KEY 
    (
        [AppID]
    ) REFERENCES [dbo].[AzMan_AzApplication] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[AzMan_AzScope] ADD 
    CONSTRAINT [FK_AzMan_AzScope_AzMan_AzApplication] FOREIGN KEY 
    (
        [AppID]
    ) REFERENCES [dbo].[AzMan_AzApplication] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
GO


ALTER TABLE [dbo].[AzMan_BizRule_To_Group] ADD 
    CONSTRAINT [FK_AzMan_BizRule_To_Group_AzMan_AzApplicationGroup] FOREIGN KEY 
    (
        [GroupID]
    ) REFERENCES [dbo].[AzMan_AzApplicationGroup] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [FK_AzMan_BizRule_To_Group_AzManBizrule] FOREIGN KEY 
    (
        [BizRuleID]
    ) REFERENCES [dbo].[AzMan_BizRule] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_BizRule_To_Group] PRIMARY KEY  CLUSTERED 
    (
        [BizRuleID],
        [GroupID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_BizRule_To_Task] ADD 
    CONSTRAINT [FK_AzMan_BizRule_To_Task_AzMan_AzTask] FOREIGN KEY 
    (
        [TaskID]
    ) REFERENCES [dbo].[AzMan_AzTask] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [FK_AzMan_BizRule_To_Task_AzManBizrule] FOREIGN KEY 
    (
        [BizRuleID]
    ) REFERENCES [dbo].[AzMan_BizRule] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_BizRule_To_Task] PRIMARY KEY  CLUSTERED 
    (
        [BizRuleID],
        [TaskID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_Group_AppMember] ADD 
    CONSTRAINT [FK_AzMan_Group_AppMember_AzMan_AzApplicationGroup] FOREIGN KEY 
    (
        [GroupID]
    ) REFERENCES [dbo].[AzMan_AzApplicationGroup] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_Group_AppMember] PRIMARY KEY  CLUSTERED 
    (
        [GroupID],
        [ChildID],
        [Member]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_Group_SIDMember] ADD 
    CONSTRAINT [FK_AzMan_Group_SIDMember_AzMan_AzApplicationGroup] FOREIGN KEY 
    (
        [GroupID]
    ) REFERENCES [dbo].[AzMan_AzApplicationGroup] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_Group_SIDMember] PRIMARY KEY  CLUSTERED 
    (
        [GroupID],
        [MemberSID],
        [Member]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_LDAPQuery] ADD 
    CONSTRAINT [FK_AzMan_LDAPQuery_AzMan_AzApplicationGroup] FOREIGN KEY 
    (
        [GroupID]
    ) REFERENCES [dbo].[AzMan_AzApplicationGroup] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[AzMan_Role_AppMember] ADD 
    CONSTRAINT [FK_AzMan_Role_AppMember_AzMan_AzRoleAssignment] FOREIGN KEY 
    (
        [RoleID]
    ) REFERENCES [dbo].[AzMan_AzRoleAssignment] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_Role_AppMember] PRIMARY KEY  CLUSTERED 
    (
        [RoleID],
        [ChildID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_Role_SIDMember] ADD 
    CONSTRAINT [FK_AzMan_Role_SIDMember_AzMan_AzRoleAssignment] FOREIGN KEY 
    (
        [RoleID]
    ) REFERENCES [dbo].[AzMan_AzRoleAssignment] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_Role_SIDMember] PRIMARY KEY  CLUSTERED 
    (
        [RoleID],
        [MemberSID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_Role_To_Operation_Link] ADD 
    CONSTRAINT [FK_AzMan_Role_To_Operation_Link_AzMan_AzOperation] FOREIGN KEY 
    (
        [OperationID]
    ) REFERENCES [dbo].[AzMan_AzOperation] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [FK_AzMan_Role_To_Operation_Link_AzMan_AzRoleAssignment] FOREIGN KEY 
    (
        [RoleID]
    ) REFERENCES [dbo].[AzMan_AzRoleAssignment] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_Role_To_Operation_Link] PRIMARY KEY  CLUSTERED 
    (
        [RoleID],
        [OperationID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[AzMan_Role_To_Task_Link] ADD 
    CONSTRAINT [FK_AzMan_Role_To_Task_Link_AzMan_AzRoleAssignment] FOREIGN KEY 
    (
        [RoleID]
    ) REFERENCES [dbo].[AzMan_AzRoleAssignment] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [FK_AzMan_Role_To_Task_Link_AzMan_AzTask] FOREIGN KEY 
    (
        [TaskID]
    ) REFERENCES [dbo].[AzMan_AzTask] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT [PK_AzMan_Role_To_Task_Link] PRIMARY KEY  CLUSTERED 
    (
        [RoleID],
        [TaskID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_Task_To_Operation_Link] ADD 
    CONSTRAINT [FK_AzMan_Task_To_Operation_Link_AzMan_AzOperation] FOREIGN KEY 
    (
        [OperationID]
    ) REFERENCES [dbo].[AzMan_AzOperation] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [FK_AzMan_Task_To_Operation_Link_AzMan_AzTask] FOREIGN KEY 
    (
        [TaskID]
    ) REFERENCES [dbo].[AzMan_AzTask] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [PK_AzMan_Task_To_Operation_Link] PRIMARY KEY  CLUSTERED 
    (
        [OperationID],
        [TaskID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AzMan_Task_To_Task_Link] ADD 
    CONSTRAINT [FK_AzMan_Task_To_Task_Link_AzMan_AzTask] FOREIGN KEY 
    (
        [TaskID]
    ) REFERENCES [dbo].[AzMan_AzTask] (
        [ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT [PK_AzMan_Task_To_Task_Link] PRIMARY KEY  CLUSTERED 
    (
        [TaskID],
        [ChildID]
    )  ON [PRIMARY]
GO

--Trigger for updating Timestamps

CREATE TRIGGER TR_updateStoreTimeStampOnAppDelete ON dbo.[AzMan_AzApplication] 
FOR delete
AS
    UPDATE [AzMan_AzAuthorizationStore] 
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT StoreID FROM deleted)
go

CREATE TRIGGER TR_updateStoreTimeStampOnAppModified ON dbo.[AzMan_AzApplication] 
FOR INSERT,UPDATE
AS

    UPDATE [AzMan_AzAuthorizationStore] 
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT StoreID FROM inserted)
go

CREATE TRIGGER TR_updateAppTimeStampOnScopeDelete ON dbo.[AzMan_AzScope] 
FOR delete
AS
    UPDATE [AzMan_AzApplication] 
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT AppID FROM deleted)
go

CREATE TRIGGER TR_updateAppTimeStampOnScoprModified ON dbo.[AzMan_AzScope] 
FOR INSERT,UPDATE
AS

    UPDATE [AzMan_AzApplication] 
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT AppID FROM inserted)
go

CREATE TRIGGER TR_UpdateAppTimeStampOnOpChange ON dbo.[AzMan_AzOperation] 
FOR INSERT,UPDATE
AS
    UPDATE [AzMan_AzApplication] 
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT AppID FROM inserted)
go

CREATE TRIGGER TR_UpdateAppTimeStampOnOpDelete ON dbo.[AzMan_AzOperation] 
FOR delete
AS
    UPDATE [AzMan_AzApplication] 
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT AppID FROM deleted)
go

Create Procedure UpdateGroupsParentTimeStamp
(
  @ParentId [int]                   
, @ParentType [tinyint]
)
as 
    if @ParentType = 0 
    Begin 
        UPDATE [AzMan_AzAuthorizationStore] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end
    else if @ParentType = 1  -- App
    Begin
        UPDATE [AzMan_AzApplication] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end
    else if @ParentType = 4  -- Scope
    Begin
        UPDATE [AzMan_AzScope] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end

GO

Create Procedure UpdateTasksParentTimeStamp
(
  @ParentId [int]                   
, @ParentType [tinyint]
)
as 
    if @ParentType = 1  -- App
    Begin
        UPDATE [AzMan_AzApplication] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end
    else if @ParentType = 4  -- Scope
    Begin
        UPDATE [AzMan_AzScope] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end
GO

Create Procedure UpdateRolesParentTimeStamp
(
  @ParentId [int]                   
, @ParentType [tinyint]
)
as 
    if @ParentType = 1  -- App
    Begin
        UPDATE [AzMan_AzApplication] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end
    else if @ParentType = 4  -- Scope
    Begin
        UPDATE [AzMan_AzScope] SET [ChildUpdateTimeStamp]=@@DBTS WHERE ID=@ParentId 
    end
GO

CREATE TRIGGER TR_UpdateParentTimeStampOnGroupDelete ON dbo.[AzMan_AzApplicationGroup] 
FOR delete
AS
DECLARE @ParentID INT
DECLARE @ParentType TinyInt
Declare @StoreID INT
Declare @AppID INT
Declare @ScopeID INT

    -- We need to find the parent first
    SELECT @StoreID=StoreID, @AppID=AppID, @ScopeID=ScopeID, @ParentType=ParentType FROM deleted
    if @@RowCount = 1
    Begin
        if @ParentType = 0
        Begin
            set @ParentID = @StoreID
        End
        else if @ParentType = 1
        Begin
            set @ParentID = @AppID
        End
        else if @ParentType = 4
        Begin
            set @ParentID = @ScopeID
        End
        
        exec UpdateGroupsParentTimeStamp @ParentID, @ParentType
    End     
go

CREATE TRIGGER TR_UpdateParentTimeStampOnGroupInsOrUpd ON dbo.[AzMan_AzApplicationGroup] 
FOR INSERT,UPDATE
AS

DECLARE @ParentID INT
DECLARE @ParentType TinyInt
Declare @StoreID INT
Declare @AppID INT
Declare @ScopeID INT

    SELECT @StoreID=StoreID, @AppID=AppID, @ScopeID=ScopeID, @ParentType=ParentType FROM inserted
    
    if @@RowCount = 1
    Begin
        if @ParentType = 0 
        Begin
            set @ParentID = @StoreID
        End
        else if @ParentType = 1 
        Begin
            set @ParentID = @AppID
        End
        else if @ParentType = 4 
        Begin
            set @ParentID = @ScopeID
        End
    
        exec UpdateGroupsParentTimeStamp @ParentID, @ParentType
    End     
go

CREATE TRIGGER TR_UpdateParentTimeStampOnTaskDelete ON dbo.[AzMan_AzTask] 
FOR delete
AS
DECLARE @ParentID INT
DECLARE @ParentType TinyInt
Declare @AppID INT
Declare @ScopeID INT

    -- We need to find the parent first
    SELECT @AppID=AppID, @ScopeID=ScopeID, @ParentType=ParentType FROM deleted
    if @@RowCount = 1
        Begin
           if @ParentType = 1 
           Begin
        set @ParentID = @AppID
       End
       else if @ParentType = 4 
       Begin
        set @ParentID = @ScopeID
       End

       exec UpdateTasksParentTimeStamp @ParentID, @ParentType
        end    
go

CREATE TRIGGER TR_UpdateParentTimeStampOnTaskInsOrUpd ON dbo.[AzMan_AzTask] 
FOR INSERT,UPDATE
AS
DECLARE @ParentID INT
DECLARE @ParentType TinyInt
Declare @AppID INT
Declare @ScopeID INT

    SELECT @AppID=AppID, @ScopeID=ScopeID, @ParentType=ParentType FROM inserted
    if @@RowCount = 1
        Begin
           if @ParentType = 1 
           Begin
        set @ParentID = @AppID
       End
       else if @ParentType = 4 
       Begin
        set @ParentID = @ScopeID
       End

       exec UpdateTasksParentTimeStamp @ParentID, @ParentType
        end
go


CREATE TRIGGER TR_UpdateParentTimeStampOnRoleDelete ON dbo.[AzMan_AzRoleAssignment] 
FOR delete
AS
DECLARE @ParentID INT
DECLARE @ParentType TinyInt
Declare @AppID INT
Declare @ScopeID INT

    -- We need to find the parent first
    SELECT @AppID=AppID, @ScopeID=ScopeID, @ParentType=ParentType FROM deleted
    if @@RowCount = 1
        Begin
           if @ParentType = 1 
           Begin
        set @ParentID = @AppID
       End
       else if @ParentType = 4 
       Begin
        set @ParentID = @ScopeID
       End
           exec UpdateRolesParentTimeStamp @ParentID, @ParentType
        end
go

CREATE TRIGGER TR_UpdateParentTimeStampOnRoleInsOrUpd ON dbo.[AzMan_AzRoleAssignment] 
FOR INSERT,UPDATE
AS
DECLARE @ParentID INT
DECLARE @ParentType TinyInt
Declare @AppID INT
Declare @ScopeID INT

    SELECT @AppID=AppID, @ScopeID=ScopeID, @ParentType=ParentType FROM inserted
    if @@RowCount = 1
        Begin
           if @ParentType = 1 
           Begin
        set @ParentID = @AppID
       End
       else if @ParentType = 4 
       Begin
        set @ParentID = @ScopeID
       End

           exec UpdateRolesParentTimeStamp @ParentID, @ParentType
        end
go

CREATE TRIGGER TR_UpdateGroupsTimeStampOnSidMemberDel ON dbo.[AzMan_Group_SIDMember] 
FOR delete
AS
    UPDATE [AzMan_AzApplicationGroup]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT GroupID FROM deleted)
go

CREATE TRIGGER TR_UpdateGroupsTimeStampOnSidMemberInsOrUpd ON dbo.[AzMan_Group_SIDMember] 
FOR INSERT,UPDATE
AS
    UPDATE [AzMan_AzApplicationGroup]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT GroupID FROM inserted)
go

CREATE TRIGGER TR_UpdateGroupsTimeStampOnAppdMemberDel ON dbo.[AzMan_Group_AppMember] 
FOR delete
AS
    UPDATE [AzMan_AzApplicationGroup]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT GroupID FROM deleted)
go

CREATE TRIGGER TR_UpdateGroupsTimeStampOnAppMemberInsOrUpd ON dbo.[AzMan_Group_AppMember] 
FOR INSERT,UPDATE
AS
    UPDATE [AzMan_AzApplicationGroup]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT GroupID FROM inserted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnSidMemberDel ON dbo.[AzMan_Role_SIDMember] 
FOR delete
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM deleted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnSidMemberInsOrUpd ON dbo.[AzMan_Role_SIDMember] 
FOR INSERT,UPDATE
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM inserted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnAppMemberDel ON dbo.[AzMan_Role_AppMember] 
FOR delete
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM deleted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnAppMemberInsOrUpd ON dbo.[AzMan_Role_AppMember] 
FOR INSERT,UPDATE
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM inserted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnOperationDelete ON dbo.AzMan_Role_To_Operation_Link
FOR DELETE
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM deleted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnOperationInsert ON dbo.AzMan_Role_To_Operation_Link
FOR INSERT
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM inserted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnTaskDelete ON dbo.AzMan_Role_To_Task_Link
FOR DELETE
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM deleted)
go

CREATE TRIGGER TR_UpdateRolesTimeStampOnTaskInsert ON dbo.AzMan_Role_To_Task_Link
FOR INSERT
AS
    UPDATE [AzMan_AzRoleAssignment]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT RoleID FROM inserted)
go

CREATE TRIGGER TR_UpdateTaskTimeStampOnOperationDelete ON dbo.AzMan_Task_To_Operation_Link
FOR DELETE
AS
    UPDATE [AzMan_AzTask]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT TaskID FROM deleted)
go

CREATE TRIGGER TR_UpdateTaskTimeStampOnOperationInsert ON dbo.AzMan_Task_To_Operation_Link
FOR INSERT
AS
    UPDATE [AzMan_AzTask]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT TaskID FROM inserted)
go

CREATE TRIGGER TR_UpdateTaskTimeStampOnTaskDelete ON dbo.AzMan_Task_To_Task_Link
FOR DELETE
AS
    UPDATE [AzMan_AzTask]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT TaskID FROM deleted)
go

CREATE TRIGGER TR_UpdateTaskTimeStampOnTaskInsert ON dbo.AzMan_Task_To_Task_Link
FOR INSERT
AS
    UPDATE [AzMan_AzTask]
        SET [ChildUpdateTimeStamp]=@@DBTS 
    WHERE ID in (SELECT TaskID FROM inserted)
go

CREATE TRIGGER TR_DeleteGroupForStore ON dbo.[AzMan_AzAuthorizationStore]
FOR delete
AS
delete [AzMan_AzApplicationGroup] where StoreID in (select ID from deleted)  
go

CREATE TRIGGER TR_DeleteGroupForApp ON dbo.[AzMan_AzApplication]
FOR delete
AS
delete [AzMan_AzApplicationGroup] where AppID in (SELECT ID FROM deleted) 
go

CREATE TRIGGER TR_DeleteGroupForScope ON dbo.[AzMan_AzScope]
FOR delete
AS
delete [AzMan_AzApplicationGroup] where ScopeID in (SELECT ID FROM deleted)
go

CREATE TRIGGER TR_DeleteTaskForApp ON dbo.[AzMan_AzApplication]
FOR delete
AS
delete [AzMan_AzTask] where AppID in (SELECT ID FROM deleted)
go

CREATE TRIGGER TR_DeleteTaskForScope ON dbo.[AzMan_AzScope]
FOR delete
AS
delete [AzMan_AzTask] where ScopeID in (SELECT ID FROM deleted)
go

CREATE TRIGGER TR_DeleteRoleForApp ON dbo.[AzMan_AzApplication]
FOR delete
AS
delete [AzMan_AzRoleAssignment] where AppID in (SELECT ID FROM deleted)
go

CREATE TRIGGER TR_DeleteRoleForScope ON dbo.[AzMan_AzScope]
FOR delete
AS
delete [AzMan_AzRoleAssignment] where ScopeID in (SELECT ID FROM deleted)
go

CREATE TRIGGER TR_DeleteBizRuleForTask ON dbo.[AzMan_BizRule_To_Task]
FOR delete
AS
delete [AzMan_BizRule] where [ID] in (SELECT BizRuleID FROM deleted)
go

CREATE TRIGGER TR_DeleteBizRuleForGroup ON dbo.[AzMan_BizRule_To_Group]
FOR delete
AS
delete [AzMan_BizRule] where [ID] in (SELECT BizRuleID FROM deleted)
go 
IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_GenerateObjectAudit' and type = 'P')
    DROP PROCEDURE [AzMan_SP_GenerateObjectAudit]
GO

Create Procedure [AzMan_SP_GenerateObjectAudit]  
(
@success int,
@event int,
@actObjectType tinyint,
@actObjectName nvarchar(512),
@actObjectGuid uniqueidentifier,
@targetType tinyint,
@targetName nvarchar(512),
@targetGuid uniqueidentifier,
@otherInfo nvarchar(1024)
)
AS
    DECLARE @UserName nvarchar(256)
    DECLARE @UserSid varbinary(85)
    SELECT @UserName = User_Name()
    SELECT @UserSid = SUSER_SID()
    
    -- events:
    -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
    -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
    -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
    -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
    
    
    Exec master.dbo.AzGenerateAudit @success,
                                    @event, 
                                    @actObjectType, 
                                    @actObjectName, 
                                    @actObjectGuid, 
                                    0, 
                                    @UserName, 
                                    @UserSid, 
                                    @targetType,  
                                    @targetName, 
                                    @targetGuid,
                                    @otherInfo

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_GenerateMemberAudit' and type = 'P')
    DROP PROCEDURE [AzMan_SP_GenerateMemberAudit]
GO

Create Procedure [AzMan_SP_GenerateMemberAudit]  
(
@success int,
@event int,
@actObjectType tinyint,
@actObjectName nvarchar(512),
@actObjectGuid uniqueidentifier,
@memberName nvarchar(256),
@memberSid varbinary(85),
@memberFlag int,
@otherInfo nvarchar(1024)
)
AS
    DECLARE @UserName nvarchar(256)
    DECLARE @UserSid varbinary(85)
    SELECT @UserName = User_Name()
    SELECT @UserSid = SUSER_SID()
    
    -- events:
    -- 4 for SE_AUDITID_AZ_SQL_MEMBER_ASSIGN
    -- 5 for SE_AUDITID_AZ_SQL_MEMBER_REMOVE
    
    -- memberFlags:
    -- 0 for non-members
    -- 1 for members
    -- 2 for admins
    -- 3 for readers
    -- 4 for delegated users
    
    
    Exec master.dbo.AzGenerateAudit @success,
                                    @event, 
                                    @actObjectType, 
                                    @actObjectName, 
                                    @actObjectGuid, 
                                    0, 
                                    @UserName, 
                                    @UserSid, 
                                    @memberName,  
                                    @memberSid, 
                                    @memberFlag,
                                    @otherInfo

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_GenerateGenericAudit' and type = 'P')
    DROP PROCEDURE [AzMan_SP_GenerateGenericAudit]
GO

Create Procedure [AzMan_SP_GenerateGenericAudit]  
(
@success int,
@actObjectType tinyint,
@actObjectName nvarchar(512),
@actObjectGuid uniqueidentifier,
@otherInfo nvarchar(1024)
)
AS
    DECLARE @UserName nvarchar(256)
    DECLARE @UserSid varbinary(85)
    SELECT @UserName = User_Name()
    SELECT @UserSid = SUSER_SID()
    
    -- events:
    -- 6 for SE_AUDITID_AZ_SQL_OTHER
    
        
    Exec master.dbo.AzGenerateAudit @success,
                                    6, 
                                    @actObjectType, 
                                    @actObjectName, 
                                    @actObjectGuid, 
                                    0, 
                                    @UserName, 
                                    @UserSid, 
                                    @otherInfo

GO


IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPU_SqlRoleUpdated' and type = 'P')
    DROP PROCEDURE AzMan_SPU_SqlRoleUpdated
GO

CREATE PROCEDURE dbo.AzMan_SPU_SqlRoleUpdated 
(
@Return int output,
@ObjectID int,
@ObjectType tinyint
)
AS

DECLARE @OldChildTS timestamp
    
    -- We can't just manually update the row timestamp because it is a timestamp column. 
    -- So we set-and-reset the ChildUpdateTimeStamp for the update of the timestamp column
    
    IF @ObjectType = 0
        BEGIN
            SELECT @OldChildTS = [ChildUpdateTimeStamp] FROM [AzMan_AzAuthorizationStore] WHERE ID = @ObjectID
            UPDATE [AzMan_AzAuthorizationStore]
                SET [ChildUpdateTimeStamp]=@OldChildTS + 1
                WHERE ID = @ObjectID
                
            UPDATE [AzMan_AzAuthorizationStore]
                SET [ChildUpdateTimeStamp]=@OldChildTS
                WHERE ID = @ObjectID
        END
    ELSE IF @ObjectType = 1
        BEGIN
            SELECT @OldChildTS = [ChildUpdateTimeStamp] FROM [AzMan_AzApplication] WHERE ID = @ObjectID
            UPDATE [AzMan_AzApplication]
                SET [ChildUpdateTimeStamp]=@OldChildTS + 1
                WHERE ID = @ObjectID
            UPDATE [AzMan_AzApplication]
                SET [ChildUpdateTimeStamp]=@OldChildTS
                WHERE ID = @ObjectID
        END
    ELSE IF @ObjectType = 4
        BEGIN
            SELECT @OldChildTS = [ChildUpdateTimeStamp] FROM [AzMan_AzScope] WHERE ID = @ObjectID
            UPDATE [AzMan_AzScope]
                SET [ChildUpdateTimeStamp]=@OldChildTS + 1
                WHERE ID = @ObjectID
            UPDATE [AzMan_AzScope]
                SET [ChildUpdateTimeStamp]=@OldChildTS
                WHERE ID = @ObjectID
        END
        
    SET @Return = @@ERROR
    
    RETURN @Return
    
GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_GetRoleMemberCount' and type = 'P')
    DROP PROCEDURE AzMan_SP_GetRoleMemberCount
GO

CREATE PROCEDURE dbo.AzMan_SP_GetRoleMemberCount 
(
@SqlRoleName nvarchar(64),
@count int output
)
AS
    select @count = count(*)  
    from sysusers u, sysusers g, sysmembers m  
    where   g.uid = m.groupuid
        and    g.name = @SqlRoleName
        and    g.issqlrole = 1  
        and    u.uid = m.memberuid
	RETURN
GO


IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_GetSQLRole' and type = 'P')
    DROP PROCEDURE AzMan_SP_GetSQLRole
GO

Create Procedure [AzMan_SP_GetSQLRole]
(
@Return [int] output,
@ID [int]
,@ObjectType [tinyint]
,@RoleType [tinyint]
,@SQLRoleName [nvarchar] (64) output
)
As
    Set @Return = 0
    
    Select @SQLRoleName = SQLRoleName from Azman_SQLRole
    where  ObjectID = @ID and ObjectType = @ObjectType and RoleType = @RoleType
Go

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPI_SQLRole' and type = 'P')
    DROP PROCEDURE AzMan_SPI_SQLRole
GO

Create PROCEDURE dbo.AzMan_SPI_SQLRole
(
 @Return [int] output
,@ID int
,@ObjectType [tinyint]
,@RoleType [tinyint]
,@SQLRoleName [nvarchar] (64) output    
)
AS
    Set @SQLRoleName = ''

    DECLARE @myid   uniqueidentifier
    DECLARE @RoleID smallint
    DECLARE @Ret    [int]
    
    declare @dbName nvarchar (255)

    Set @Return = 0


    SET @myid = NEWID()
    Set @SQLRoleName = CONVERT(varchar(64), @myid)
    Set @RoleID = 1 
    
    If Is_Member('db_owner') = 1 or Is_Member('db_securityadmin') = 1
    Begin
        EXEC @Ret = sp_addrole @SQLRoleName
    End
    else
    begin
        Set @dbName = db_name()
        Exec master.dbo.xp_AzManAddRole @Ret output, @ID, @ObjectType, @dbName, @SQLRoleName
    End
    
    if @Ret = 0 
        Begin
            select @RoleID = uid from sysusers where (name = @SQLRoleName) and (issqlrole = 1)
            
            Insert Into [dbo].[Azman_SQLRole]
            (
                [uid],      
                [ObjectID], 
                [ObjectType],
                [SQLRoleName],
                [RoleType] 
            )
            Values
            (
                @RoleID,
                @ID,
                @ObjectType,
                @SQLRoleName,
                @RoleType
            )
        End     
    Else
        Begin
            Set @Return = -1
        End

    Return @Return  
go

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPI_SQLRole_From_XP' and type = 'P')
    DROP PROCEDURE AzMan_SPI_SQLRole_From_XP
GO

Create PROCEDURE dbo.AzMan_SPI_SQLRole_From_XP
(
 @Return [int] output
,@SQLRoleName [nvarchar] (64) 
)
AS

    DECLARE @RoleID smallint
    DECLARE @Ret    [int]

    Set @Return = -1
    Set @RoleID = -1

    EXEC @Ret = sp_addrole @SQLRoleName
    
    if @Ret = 0 
        Begin
            select @RoleID = uid from sysusers where (name = @SQLRoleName) and (issqlrole = 1)
            if @RoleID <> -1
            Begin
                set @Return = 0
            End
        End     
    Else
        Begin
            Set @Return = -1
        End

    Return @Return  
go

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPI_Add_User_To_SQLRole_From_XP' and type = 'P')
    DROP PROCEDURE AzMan_SPI_Add_User_To_SQLRole_From_XP
GO

CREATE PROCEDURE AzMan_SPI_Add_User_To_SQLRole_From_XP
(
 @Return             [int] output
,@ID                 [int]
,@ObjectType         [tinyint]
,@RoleType           [tinyint]
,@UserName          [nvarchar] (255)
)
AS
    DECLARE @Ret                [int]
    Declare @NameInDB           [nvarchar] (255)
    Declare @SQLRoleName        [nvarchar] (64) 
    DECLARE @RoleMemberCount    [int]
    Set @Return = -1    
    
    Set @SQLRoleName = NULL
    
    Exec dbo.[AzMan_SP_GetSQLRole] @Ret output, 
            @ID, 
            @ObjectType, 
            @RoleType, 
            @SQLRoleName = @SQLRoleName output  

    if len(@SQLRoleName) > 0
    Begin
    
        set @NameInDB = null                          

        SET @Ret = 0

        -- if no such loging yet, then grant the user login
        IF NOT EXISTS (SELECT * FROM master..syslogins WHERE UPPER(loginname) = UPPER(@UserName) AND hasaccess=1)
            BEGIN
                EXEC @Ret = sp_grantlogin @UserName
            END

        -- if the user does not have access to the db
        IF @Ret= 0 AND NOT EXISTS (SELECT * FROM sysusers WHERE UPPER(Name) = UPPER(@UserName) AND hasdbaccess = 1)
            BEGIN
                EXEC @Ret = sp_grantdbaccess @UserName, @NameInDB output
            END
            
        -- workaround for now sp_grantdbaccess returns 1 when the user already has access
        if @Ret= 0 or len(@NameInDB) > 0
            BEGIN
                Exec @Ret = sp_addrolemember  @SQLRoleName, @UserName
                -- if the user is successfully added to a scope's role
                IF @Ret = 0 AND @ObjectType = 4 -- scope
                    BEGIN
                        EXEC AzMan_SP_GetRoleMemberCount @SQLRoleName, @RoleMemberCount output
                        IF @RoleMemberCount > 0
	                        UPDATE dbo.AzMan_AzScope SET dbo.AzMan_AzScope.HasSpecificUsers = 1 
	                            WHERE dbo.AzMan_AzScope.ID = @ID
                    END
            END
    End
        
    Set @Return = @Ret 
GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPI_Add_User_To_SQLRole' and type = 'P')
    DROP PROCEDURE AzMan_SPI_Add_User_To_SQLRole
GO

CREATE PROCEDURE AzMan_SPI_Add_User_To_SQLRole
(
 @Return             [int] output
,@ID                 [int]
,@ObjectType         [tinyint]
,@RoleType           [tinyint]
,@SQLRoleName        [nvarchar] (64) 
,@UserSid            varbinary(85)
,@UserName          [nvarchar] (255)
,@SaclIsOn          [bit]
)
AS
    DECLARE @Ret    [int]
    Declare @NameInDB [nvarchar] (255)
    DECLARE @ObjectName nvarchar(512)
    DECLARE @ObjectGuid uniqueidentifier
    DECLARE @MemberType int    
    declare @dbName nvarchar (255)
    DECLARE @RoleMemberCount    [int]
    
    Set @Return = -1

    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            IF @ObjectType = 0 -- store
                SELECT @ObjectName = store.Name, @ObjectGuid=store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] store
                    WHERE store.ID=@ID
            IF @ObjectType = 1 -- app
                SELECT @ObjectName = app.Name, @ObjectGuid=app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                    WHERE app.ID=@ID
            IF @ObjectType = 4 -- scope
                SELECT @ObjectName = scope.Name, @ObjectGuid=scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                    WHERE scope.ID=@ID
            
            IF @RoleType = 1  -- admin
                SET @MemberType = 2
            IF @RoleType = 2  -- reader
                SET @MemberType = 3
            IF @RoleType = 3  -- delegated users
                SET @MemberType = 4
        END
                     
    set @NameInDB = null
    If Is_Member('db_owner') = 1
    Begin
        exec AzMan_SPI_Add_User_To_SQLRole_From_XP @Return output, @ID, @ObjectType, @RoleType, @UserName
    End
    else
    Begin
        Set @dbName = db_name()
        Exec master.dbo.xp_AzManAddUserToRole @Return output, @ID, @ObjectType, @RoleType, @dbName, @UserName
    End
    
    -- trigger the parent object's update timestamp
    IF @Return = 0
        BEGIN
            Exec AzMan_SPU_SqlRoleUpdated @Ret output, @ID, @ObjectType
            IF @ObjectType = 4 -- scope
                BEGIN
                    EXEC AzMan_SP_GetRoleMemberCount @SQLRoleName, @RoleMemberCount output
                    IF @RoleMemberCount > 0
	                    UPDATE dbo.AzMan_AzScope SET dbo.AzMan_AzScope.HasSpecificUsers = 1 
	                        WHERE dbo.AzMan_AzScope.ID = @ID
                END
        END
        
    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateMemberAudit]
                @Return,
                4,  -- 0 for SE_AUDITID_AZ_SQL_MEMBER_ASSIGN
                @ObjectType,
                @ObjectName,
                @ObjectGuid,
                @UserName,
                @UserSid,
                @MemberType,  -- member type
                N'' -- no other info
        END
            
	Return @Return

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPI_Create_SqlRole_For_Object' and type = 'P')
    DROP PROCEDURE AzMan_SPI_Create_SqlRole_For_Object
GO

CREATE PROCEDURE AzMan_SPI_Create_SqlRole_For_Object
(
 @Return                [int] output
,@ID                    [int]
,@ObjectType        [tinyint]
,@RoleType          [tinyint]  
)
AS
    DECLARE @Ret            [int]
    DECLARE @SQLRoleName    [nvarchar] (255)
    
    DECLARE @SaclIsOn [bit]
    DECLARE @AccessAtObjType tinyint
    Set @SaclIsOn = 0
    Set @Return = 0
    
    Exec dbo.[AzMan_SP_GetSQLRole] @Ret output, 
            @ID, 
            @ObjectType, 
            @RoleType, 
            @SQLRoleName = @SQLRoleName output  
    
    if @SQLRoleName Is NULL
        begin
            Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output, @ID, @ObjectType, 1, @SaclIsOn output
            if @Return <> 1
                begin
                    Set @Return = -5
                    goto Done
                end
            -- we have proper access, then insert the role
            Exec AzMan_SPI_SQLRole @Return output, @ID, @ObjectType, @RoleType, @SQLRoleName = @SQLRoleName output
        end
    
Done:

    Return @Return  

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPI_Add_User_To_Role' and type = 'P')
    DROP PROCEDURE AzMan_SPI_Add_User_To_Role
GO

CREATE PROCEDURE AzMan_SPI_Add_User_To_Role
(
 @Return                [int] output
,@ID                    [int]
,@ObjectType        [tinyint]
,@RoleType          [tinyint]
,@UserSid           varbinary(85)
,@UserName          [nvarchar] (255)    
)
AS
    DECLARE @Ret            [int]
    DECLARE @SQLRoleName    [nvarchar] (255)
    Declare @NameInDB       [nvarchar] (255)
    
    DECLARE @SaclIsOn [bit]
    DECLARE @AccessAtObjType tinyint
    Set @SaclIsOn = 0
    Set @Return = 0
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output, @ID, @ObjectType, 1, @SaclIsOn output
    
    IF @Return <> 1
        BEGIN
            Set @Return = -5
            goto Done
        END
        
    Set @Return = -1

    Exec dbo.[AzMan_SP_GetSQLRole] @Ret output, 
            @ID, 
            @ObjectType, 
            @RoleType, 
            @SQLRoleName = @SQLRoleName output  

    if @SQLRoleName  Is NULL
    Begin
        Exec AzMan_SPI_SQLRole @Return output, @ID, @ObjectType, @RoleType, @SQLRoleName = @SQLRoleName output
    End
    
    if @SQLRoleName is NOT NULL
    Begin 
        Exec AzMan_SPI_Add_User_To_SQLRole @Return output, @ID, @ObjectType, @RoleType, @SQLRoleName, @UserSid, @UserName, @SaclIsOn
    End
    Else
    Begin
        Set @Return = -1
    End     

Done:

    Return @Return  

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPD_User_From_SQLRole_From_XP' and type = 'P')
    DROP PROCEDURE AzMan_SPD_User_From_SQLRole_From_XP
GO

CREATE PROCEDURE AzMan_SPD_User_From_SQLRole_From_XP
(
@Return              [int] output
,@SQLRoleName        [nvarchar] (64)
,@UserName           [nvarchar] (255)
)
AS
    DECLARE @Ret    [int]
    
    SET NOCOUNT ON
    
    Set @Return = -1
    
    Exec @Ret = sp_droprolemember @SQLRoleName, @UserName
    
    if @Ret <> 0 
    Begin
        Set @Return = -1 
    End
    Else
    Begin
        Set @Return = 0 
    End
    
    SET NOCOUNT OFF
    
    Return @Return  
GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPD_User_From_SQLRole' and type = 'P')
    DROP PROCEDURE AzMan_SPD_User_From_SQLRole
GO

CREATE PROCEDURE AzMan_SPD_User_From_SQLRole
(
 @Return             [int] output
,@ID                 [int]
,@ObjectType         [tinyint]
,@RoleType           [tinyint]
,@SQLRoleName        [nvarchar] (64)
,@UserSid            varbinary(85)
,@UserName           [nvarchar] (255)
,@SaclIsOn           [bit]
)
AS
    DECLARE @Ret    [int]
    DECLARE @ObjectName nvarchar(512)
    DECLARE @ObjectGuid uniqueidentifier
    DECLARE @MemberType int 
    Declare @DbName [nvarchar] (255)    
    DECLARE @RoleMemberCount int
    DECLARE @OtherRoleMemberCount int
    
    DECLARE @OtherRoleName [nvarchar] (64)
    
    Set @Return = -1
    
    If is_Member('db_owner') = 1 or is_Member('db_securityadmin')  = 1 or is_member('db_accessyadmin') = 1
    Begin
        Exec @Ret = sp_droprolemember @SQLRoleName, @UserName
    End
    else
    begin
        Set @DbName = db_name()
        Exec master.dbo.xp_AzManRemoveUserFromRole 
                    @Ret output,
                    @ID,
                    @ObjectType,
                    @DbName,
                    @SQLRoleName,
                    @UserName
    End
    
    if @Ret <> 0 
    Begin
        Set @Return = -1 
    End
    Else
    Begin
        -- trigger the parent object's update timestamp
        Set @Return = 0
        Exec AzMan_SPU_SqlRoleUpdated @Return output, @ID, @ObjectType
        
        -- if the delete is successful to a scope role
        IF @Return = 0 AND @ObjectType = 4
            BEGIN
                -- we need to get the other role's name for the scope
                SET @OtherRoleName = NULL
                IF @RoleType = 1 -- admin
                    Exec [AzMan_SP_GetSQLRole] @Return output, @ID, @ObjectType, 2, @OtherRoleName output
                ELSE IF @RoleType = 2 -- reader
                    Exec [AzMan_SP_GetSQLRole] @Return output, @ID, @ObjectType, 1, @OtherRoleName output  
			    
                SET @RoleMemberCount = 0
                SET @OtherRoleMemberCount = 0
                IF @SQLRoleName IS NOT NULL
			        EXEC AzMan_SP_GetRoleMemberCount @SQLRoleName, @RoleMemberCount output
                IF @OtherRoleName IS NOT NULL
                    EXEC AzMan_SP_GetRoleMemberCount @OtherRoleName, @OtherRoleMemberCount output
                
                IF @RoleMemberCount = 0 AND @OtherRoleMemberCount = 0
	                UPDATE dbo.AzMan_AzScope SET dbo.AzMan_AzScope.HasSpecificUsers = 0 
	                    WHERE dbo.AzMan_AzScope.ID = @ID
            END
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            IF @ObjectType = 0 -- store
                SELECT @ObjectName = store.Name, @ObjectGuid=store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] store
                    WHERE store.ID=@ID
            IF @ObjectType = 1 -- app
                SELECT @ObjectName = app.Name, @ObjectGuid=app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                    WHERE app.ID=@ID
            IF @ObjectType = 4 -- scope
                SELECT @ObjectName = scope.Name, @ObjectGuid=scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                    WHERE scope.ID=@ID
                    
            IF @RoleType = 1  -- admin
                SET @MemberType = 2
            IF @RoleType = 2  -- reader
                SET @MemberType = 3
            IF @RoleType = 3  -- delegated users
                SET @MemberType = 4

            -- generate an audit
            EXEC [AzMan_SP_GenerateMemberAudit]
                @Return,
                5,  -- 0 for SE_AUDITID_AZ_SQL_MEMBER_REMOVE
                @ObjectType,
                @ObjectName,
                @ObjectGuid,
                @UserName,
                @UserSid,
                @MemberType,  -- member type
                N'' -- no other info
        END
	
    Return @Return  

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPD_User_From_Role' and type = 'P')
    DROP PROCEDURE AzMan_SPD_User_From_Role
GO

CREATE PROCEDURE AzMan_SPD_User_From_Role
(
 @Return                [int] output
,@ID                    [int]
,@ObjectType            [tinyint]
,@RoleType              [tinyint]
,@UserSid               varbinary(85)
,@UserName              [nvarchar] (255)    
)
AS
    DECLARE @Ret            [int]
    DECLARE @SQLRoleName    [nvarchar] (64) 
    DECLARE @SaclIsOn [bit]
    DECLARE @AccessAtObjType tinyint
    Set @SaclIsOn = 0
    Set @Return = 0
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output, @ID, @ObjectType, 1, @SaclIsOn output
    
    IF @Return <> 1
        BEGIN
            Set @Return = -5
            goto Done
        END

    Set @Return = -1

    Exec [AzMan_SP_GetSQLRole] @Return output, @ID, @ObjectType, @RoleType, @SQLRoleName = @SQLRoleName output  
    
    if @SQLRoleName Is not NULL
    Begin
        Exec AzMan_SPD_User_From_SQLRole @Return output, @ID, @ObjectType, @RoleType, @SQLRoleName, @UserSid, @UserName, @SaclIsOn
    End
    
Done:
    Return @Return  

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPS_Get_DBOwners' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Get_DBOwners
GO

CREATE PROCEDURE AzMan_SPS_Get_DBOwners
(
@Return [int] output,
@ID     [int]
)
AS
    SET NOCOUNT ON
    -- @ID is not used. But our internal implementation requires non-empty
    -- parameter set where @Return is not considered one of them.
    EXEC @Return = sp_helprolemember 'db_owner'
    
    SET NOCOUNT OFF
    
    Return @Return 

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPS_Get_Role_For_Object' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Get_Role_For_Object
GO

CREATE PROCEDURE AzMan_SPS_Get_Role_For_Object
(
@Return      [int] output
,@ID         [int]
,@ObjectType [tinyint]
,@RoleType   [tinyint]
)
AS
    DECLARE @SQLRoleName [nvarchar] (64)

    SET NOCOUNT ON
    
    Set @SQLRoleName = NULL
    Set @Return = 0

    Select @SQLRoleName = SQLRoleName From [dbo].[Azman_SQLRole]
    where 
      [ObjectID]    = @ID and
      [ObjectType]  = @ObjectType and
      [RoleType]    = @RoleType
    
    if @SQLRoleName is not NULL 
        Begin
            EXEC @Return = sp_helprolemember @SQLRoleName
        End
    
    SET NOCOUNT OFF
    
    Return @Return
    
GO


IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_Get_Object_Path_For_Container' and type = 'P')
    DROP PROCEDURE AzMan_SP_Get_Object_Path_For_Container
GO

Create Procedure [AzMan_SP_Get_Object_Path_For_Container]
(
 @Return                [int]   output
,@ID                    [int]
,@ObjectType            [tinyint]
,@StoreID               [int]   output
,@AppID                 [int]   output
,@ScopeID               [int]   output
)
AS
    DECLARE @RowCount INT, @Error INT
    Declare @ParentID [int]
    Declare @ParentType [int]

    Set @StoreID  = -1
    Set @AppID    = -1
    Set @ScopeID  = -1
    Set @Return   = 0 
    
    if @ObjectType = 0 --Store
    Begin
        Set @StoreID = @ID
    End 
    if @ObjectType = 1 -- App
    Begin
        Select @StoreID=StoreID From AzMan_AzApplication where ID = @ID
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        
        if @RowCount <> 1
        Begin
            if @Error = 0
            Begin
                Set @Return = -1168 -- Record not found
            End
            Else
            Begin
                Set @Return = @Error 
            End
        End
        else
        Begin
            Set @AppID = @ID        
        End
    End

    If @ObjectType = 4 -- Scope
    Begin
        Select @AppID   = AppID From [AzMan_AzScope] where ID = @ID
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
        if @RowCount = 1
        Begin
            Select @StoreID = StoreID From AzMan_AzApplication where ID = @AppID
            
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT        
            if @RowCount <> 1
            Begin
                if @Error = 0
                Begin
                    Set @Return = -1168 -- Record not found
                End
                Else
                Begin
                    Set @Return = @Error 
                End
            End
            else
            Begin
                Set @ScopeID = @ID
            End
        End
        else
        Begin
            if @Error = 0
            Begin
                Set @Return = -1168 -- Record not found
            End
            Else
            Begin
                Set @Return = @Error 
            End
        End
    End     
    
Return @Return

Go  

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_Get_Object_Path' and type = 'P')
    DROP PROCEDURE AzMan_SP_Get_Object_Path
GO

Create Procedure [AzMan_SP_Get_Object_Path]
(
 @Return [int] output
,@ID [int]
,@ObjectType [tinyint]
,@StoreID [int] output
,@AppID [int]   output
,@ScopeID [int] output
)
AS
    Declare @ParentID [int]
    Declare @ParentType [int]
    DECLARE @RowCount INT, @Error INT

    Set @StoreID  = -1
    Set @AppID    = -1
    Set @ScopeID  = -1
    Set @Return   = 0 
    
    if @ObjectType = 0 --Store
    Begin
        Exec AzMan_SP_Get_Object_Path_For_Container @Return output, @ID, @ObjectType , @StoreID output, @AppID output, @ScopeID output
        goto Return_Get_ObjPath     
    End 
    
    if @ObjectType = 1 -- App
    Begin
        Exec AzMan_SP_Get_Object_Path_For_Container @Return output, @ID, @ObjectType , @StoreID output, @AppID output, @ScopeID output
        goto Return_Get_ObjPath     
    End
    
    If @ObjectType = 2 -- Operation
    Begin
        Select @AppID=   AppID From [AzMan_AzOperation] where ID = @ID
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT        
        if @RowCount <> 1
        Begin
            if @Error = 0
            Begin
                Set @Return = -1168 -- Record not found
            End
            Else
            Begin
                Set @Return = @Error 
            End
        End
        Else
        Begin
            Select @StoreID =StoreID From AzMan_AzApplication where ID = @AppID
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
            if @RowCount <> 1
            Begin
                if @Error = 0
                Begin
                    Set @Return = -1168 -- Record not found
                End
                Else
                Begin
                    Set @Return = @Error 
                End
            End
        End
        
        goto Return_Get_ObjPath     
    End     
    
    If @ObjectType = 4 -- Scope
    Begin
        Exec AzMan_SP_Get_Object_Path_For_Container @Return output, @ID, @ObjectType , @StoreID output, @AppID output, @ScopeID output
        goto Return_Get_ObjPath
    End     

    If @ObjectType = 3 -- Task
    Begin
        Select @ParentID = 
                case ParentType
                    when 1 then AppID
                    when 4 then ScopeID
                end,
               @ParentType = ParentType from [AzMan_AzTask] where ID = @ID
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT               
        
    End

    If @ObjectType = 5 -- Group
    Begin
        Select @ParentID = 
                case ParentType
                    when 0 then StoreID
                    when 1 then AppID
                    when 4 then ScopeID 
                end,
              @ParentType = ParentType 
        from [AzMan_AzApplicationGroup] where ID = @ID
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT        
    End
    
    If @ObjectType = 6 -- Role
    Begin
        Select @ParentID =  
                case ParentType
                    when 1 then AppID
                    when 4 then ScopeID
                end,
            @ParentType = ParentType 
        from AzMan_AzRoleAssignment where ID = @ID
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT        
    End
    

    if @RowCount <> 1
    Begin
        if @Error = 0
        Begin
            Set @Return = -1168 -- Record not found
        End
        Else
        Begin
            Set @Return = @Error 
        End
    End
    else
    Begin
        exec AzMan_SP_Get_Object_Path_For_Container @Return output, @ParentID, @ParentType, @StoreID output, @AppID output, @ScopeID output
    End 
    
Return_Get_ObjPath:
    Return @Return

Go  

-- Access Check Store Procedure

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_AccessCheck_For_Container' and type = 'P')
    DROP PROCEDURE AzMan_SP_AccessCheck_For_Container
GO

Create Procedure [AzMan_SP_AccessCheck_For_Container]
(
 @Return [int] output
,@ID [int] 
,@ObjectType [tinyint]
)

As
    -- Check MemberShip starting with current node and going up
    Declare @SQLRoleName     [nvarchar](64) 
    Declare @SQLRoleType     [TinyInt]  
    
    Set @Return      = -5

    -- Check Admin
    
    Set @SQLRoleType = 1
    Set @SQLRoleName = ''
    
    Exec [AzMan_SP_GetSQLRole] @Return output, @ID, @ObjectType, @SQLRoleType, @SQLRoleName = @SQLRoleName output

    -- Is a member of admin 
    
    if @SQLRoleName <> ''
    Begin
        If Is_Member(@SQLRoleName) = 1
        Begin
            Set @Return = 1 -- Admin Access
            Goto Exit_AzMan_SP_AccessCheck_For_Container
        End
    End

    -- Is a member of Reader?

    Set @SQLRoleType = 2
    Set @SQLRoleName = ''   
    
    Exec [AzMan_SP_GetSQLRole] @Return output, @ID, @ObjectType, @SQLRoleType, @SQLRoleName = @SQLRoleName output
    
    if @SQLRoleName <> ''
    Begin
        If Is_Member(@SQLRoleName) = 1
        Begin
            Set @Return = 2 -- Reader Access
            Goto Exit_AzMan_SP_AccessCheck_For_Container
        End
    End
    
    
    -- Is a member of Delegated Users?

    Set @SQLRoleType = 3
    Set @SQLRoleName = ''   
    
    Exec [AzMan_SP_GetSQLRole] @Return output, @ID, @ObjectType, @SQLRoleType, @SQLRoleName = @SQLRoleName output
    
    if @SQLRoleName <> ''
    Begin
        If Is_Member(@SQLRoleName) = 1
        Begin
            Set @Return = 3 -- Delegated user Access
            Goto Exit_AzMan_SP_AccessCheck_For_Container
        End
    End
    
    
Exit_AzMan_SP_AccessCheck_For_Container:
    Return  @Return
GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_AccessCheck' and type = 'P')
    DROP PROCEDURE AzMan_SP_AccessCheck
GO

Create Procedure [AzMan_SP_AccessCheck]
(
 @Return [int] output
,@AccessAtObjType [tinyint] output 
,@ID [int] 
,@ObjectType [tinyint]
,@CheckSacl [bit] = 0
,@SaclIsOn [bit] output
)

As
    -- Check MemberShip starting with current node and going up
    Declare @SQLRoleName     [nvarchar](64) 
    Declare @SQLRoleType     [TinyInt]  
    
    Declare @StoreID        [int] 
    Declare @AppID          [int]   
    Declare @ScopeID        [int] 
    Declare @StoreAccess    [int]
    
    -- If this bit is set we know access check result, but we need to do SACL
    Declare @CheckSaclOnly  [int]
    Declare @TempRet [int]
    
    Set @Return      = -5
    Set @StoreAccess = -5   
    Set @AccessAtObjType = 0
    
    -- default to SACL off
    Set @SaclIsOn = 0
    Set @CheckSaclOnly = 0
    
    -- If the user is a DB_Owner, he has full control
    
    if  Is_Member('db_owner') = 1
        Begin
            set @Return = 1
            --print 'User is member of db_Owner'
            IF @CheckSacl = 0
                Goto Exit_AzMan_SP_AccessCheck
            ELSE
                Set @CheckSaclOnly = 1
        End
    else if  Is_Member('db_datawriter') = 1
        -- If the user is a data writer he has full control
        Begin
            set @Return = 1
            --print 'User is member of db_datawriter' 
            IF @CheckSacl = 0
                Goto Exit_AzMan_SP_AccessCheck
            ELSE
                Set @CheckSaclOnly = 1
        End
    else if  Is_Member('db_datareader') = 1
        -- If the user is a data reader he has read control
        Begin
            set @Return = 2
            --print 'User is member of db_datareader' 
            IF @CheckSacl = 0
                Goto Exit_AzMan_SP_AccessCheck
            ELSE
                Set @CheckSaclOnly = 1
        End
    
    IF @CheckSaclOnly = 1
        Set @TempRet = @Return
        
    -- Go all the way up to the store
    -- Check Access from top to bottom
    
    Exec AzMan_SP_Get_Object_Path @Return output, @ID, @ObjectType , @StoreID output, @AppID output, @ScopeID output    
    
    -- Error ? Record not found 
    if @Return < 0
    Begin
        goto Exit_AzMan_SP_AccessCheck  
    End
    
    -- we honor application's SACL setting over the store's
    IF @CheckSacl = 1 AND @StoreID <> -1 AND @AppID <> -1
        BEGIN
            SELECT @SaclIsOn = IsNull(ApplyStoreSacl, 0) FROM AzMan_AzApplication App WHERE App.ID = @AppID AND App.StoreID = @StoreID
        END
    ELSE IF @CheckSacl = 1 AND @StoreID <> -1
        BEGIN
            SELECT @SaclIsOn = IsNull(ApplyStoreSacl, 0) FROM AzMan_AzAuthorizationStore Store WHERE Store.ID = @StoreID
        END
            
    -- if we are only to check SACL settings (in other words, we already know the answer of access)
    IF @CheckSaclOnly = 1
        Goto Exit_AzMan_SP_AccessCheck
        
    if @StoreID <> -1
    Begin
        set @AccessAtObjType = 0
        Exec AzMan_SP_AccessCheck_For_Container @Return output, @StoreID, 0
        Set @StoreAccess = @Return
        if @Return <= 2
        Begin
            Goto Exit_AzMan_SP_AccessCheck
        End
    End
    else
    Begin
        Goto Exit_AzMan_SP_AccessCheck      
    End
    
    if @AppID <> -1
    Begin
        set @AccessAtObjType = 1
        Exec AzMan_SP_AccessCheck_For_Container @Return output, @AppID, 1
        if @Return <= 2
        Begin
            Goto Exit_AzMan_SP_AccessCheck
        End
    End
    else
    Begin
        Goto Exit_AzMan_SP_AccessCheck  
    End

    if @ScopeID <> -1
    Begin
        set @AccessAtObjType = 4
        Exec AzMan_SP_AccessCheck_For_Container @Return output, @ScopeID, 4
        if @Return <= 2 
        Begin
            Goto Exit_AzMan_SP_AccessCheck
        End
    End
    Begin
        Goto Exit_AzMan_SP_AccessCheck  
    End
    
    
Exit_AzMan_SP_AccessCheck:

    IF @CheckSaclOnly = 1
        Set @Return = @TempRet
        
    Return  @Return


GO

Create PROCEDURE dbo.AzMan_SPS_Get_StoreIDByName
(
    @Return [int] output,
    @ID int OUTPUT,    
    @Name nvarchar(512)
)
AS
Set @ID = -1    -- this means it doesn't exist
Set @Return = 0 

SELECT @ID = ID FROM AzMan_AzAuthorizationStore WHERE Name = @Name

RETURN @@RowCount

go

Create Procedure [AzMan_SP_Check_Dup_Task_For_Given_Parent]
(
    @Return [int] output,
    @ParentID	[int],
    @ParentType     [tinyint] ,
    @ID [int],
    @Name           [nvarchar](64) 
)
AS

Set @Return = 0

    -- Make sure that the name doesn't exist at the same level
    
    if @ParentType = 1
    Begin
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzTask] 
        where 
            ParentType = @ParentType and
            AppID      = @ParentID   and        
            ID <> IsNULL(@ID,0) and
            LOWER([Name]) = LOWER(@Name) )
        Begin
            Set @Return = -183  -- Same Name exist
        End
    End
    else if @ParentType = 4
    Begin
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzTask] 
        where 
            ParentType = @ParentType and
            ScopeID    = @ParentID   and        
            ID <> IsNULL(@ID,0) and
            LOWER([Name]) = LOWER(@Name) )
        Begin
            Set @Return = -183  -- Same Name exist
        End
    End
    else
    Begin
        Set @Return = -87   -- invalid parameter
    End
Return @Return

go 

Create Procedure [AzMan_SP_Check_Dup_Task]
(
    @Return [int] output,
    @ParentID	[int],
    @ParentType     [tinyint] ,
    @ID [int],
    @Name           [nvarchar](64) 
)
AS

Declare @ParentAppID [int]

Set @Return = 0
Set @ParentAppID = 0

    -- First Check the current Parent
    exec AzMan_SP_Check_Dup_Task_For_Given_Parent @Return output, @ParentID, @ParentType, @ID, @Name
    
    if  @Return <> 0 
    Begin
        Return(@Return)
    End
    
    -- Behave differently depending on the object type of the parent object
    
    -- A task that is a child of an application
    --  cannot have the same name as any tasks that are children of any of the child scopes.
    
    if @ParentType = 1		-- Application
    Begin
        Set @ParentAppID = @ParentID
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzTask] 
        where 
            ( ParentType = 4 ) -- All Child scope
                and 
                LOWER([Name]) = LOWER(@Name)  and 
                (@ParentID IN ( Select AppID from AzMan_AzScope where AppID = @ParentID and ID <> IsNULL(@ID,0)) ) )
            
        Begin
            Set @Return = -183  -- Same Name exist
        End
    End
    else if @ParentType = 4		-- Scope
    Begin
    -- A task that is a child of a scope,
    --  cannot have the same name as tasks that are children of the application.
    
        -- Find the Parent App
        Select @ParentAppID = [AppID] 
        from AzMan_AzScope
        where [ID] = @ParentID
        
        if ( @ParentAppID = 0 )
        Begin
            Set @Return = -1 -- General Error
        End
        else
        Begin
            exec AzMan_SP_Check_Dup_Task_For_Given_Parent @Return output, @ParentAppID, 1, @ID, @Name
        End
    End
    
    -- Tasks and operations share a namespace so ensure there 
    -- isn't an operation by this name.			
    if  @Return = 0 
    Begin
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzOperation] 
        where 
            LOWER([Name]) = LOWER(@Name) and
            (@ParentAppID = AppID) )
        Begin
            Set @Return = -183  -- Same Name exist
        End
    End
    

Return @Return

go 

Create Procedure [AzMan_SP_Check_Dup_RoleAssignment]
(
    @Return [int] output,
    @ParentID   [int],
    @ParentType [tinyint],
    @ID [int],
    @Name [nvarchar](64) 
)
AS

Set @Return = 0

    if @ParentType = 1
    Begin   
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzRoleAssignment] 
        where 
            ParentType = @ParentType and
            AppID =   @ParentID and
            ID <> IsNULL(@ID,0) and
            LOWER([Name]) = LOWER(@Name) )
        Begin
            Set @Return = -183  -- Same Name exist
        End
    End
    else if @ParentType = 4
    Begin
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzRoleAssignment] 
        where 
            ParentType = @ParentType and
            ScopeID =   @ParentID and
            ID <> IsNULL(@ID,0) and
            LOWER([Name]) = LOWER(@Name) )
        Begin
            Set @Return = -183  -- Same Name exist
        End
    End
    Else
    Begin
        Set @Return = -1
    End

Return @Return

go 

Create Procedure [AzMan_SP_Check_Dup_Application]
(
    @Return [int] output,
    @StoreID    [int],
    @ID [int],
    @Name [nvarchar](512) 
)
AS



Set @Return = 0

    -- Make sure that the name doesn't exist at the same level
    
    if Exists( Select [Name] from 
        [dbo].[AzMan_AzApplication] 
    where 
        StoreID =   @StoreID and ID <> IsNULL(@ID,0) and
        LOWER([Name]) = LOWER(@Name) )
    Begin
        Set @Return = -183  -- Same Name exist
    End

Return @Return

go 


Create Procedure [AzMan_SP_Check_Dup_Scope]
(
    @Return [int] output,
    @AppID  [int],
    @ID [int],
    @NameHash [binary] (32)
)
AS
    Set @Return = 0
    -- Make sure that the name doesn't exist at the same level

    begin
        if Exists( Select ID from 
            [dbo].[AzMan_AzScope] 
        where 
            ID          <>  IsNULL(@ID,0) and
            AppID       =   @AppID and
            [NameHash]  = @NameHash )
        Begin
            Set @Return = -183  -- Same Name exist
        End
    end

Return @Return



go

Create Procedure [AzMan_SP_Check_Dup_Operation]
(
    @Return [int] output,
    @AppID  [int],
    @ID [int],
    @Name [nvarchar](64) 
)
AS

Set @Return = 0

    -- Make sure that the name doesn't exist at the same level
    
    if Exists( Select [Name] from 
        [dbo].[AzMan_AzOperation] 
    where 
        AppID =   @AppID and
        ID <> IsNULL(@ID,0) and 
        LOWER([Name]) = LOWER(@Name) )
    Begin
        Set @Return = -183  -- Same Name exist
    End

Return @Return

go 


Create Procedure [AzMan_SP_Check_Dup_Group_For_Given_Parent]
(
    @Return [int] output,
    @ParentID   [int],
    @ParentType [tinyint] ,
    @ID [int],
    @Name [nvarchar](64) 
)
AS
    Set @Return = 0
        
    if @ParentType = 0
    Begin
        
    if Exists( Select [Name] from 
        [dbo].[AzMan_AzApplicationGroup] 
    where 
        ( ParentType = @ParentType ) 
        and (StoreID = @ParentID)
        and LOWER([Name]) = LOWER(@Name) 
        and ID <> IsNULL(@ID,0) )
    Begin
        Set @Return = -183  -- Same Name exist
    End
        
    End
    else if @ParentType = 1
    Begin
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzApplicationGroup] 
        where 
            ( ParentType = @ParentType ) 
            and (AppID = @ParentID)
            and LOWER([Name]) = LOWER(@Name) 
            and ID <> IsNULL(@ID,0) )
        Begin
            Set @Return = -183  -- Same Name exist
        End

    End
    else if @ParentType = 4
    Begin
        if Exists( Select [Name] from 
            [dbo].[AzMan_AzApplicationGroup] 
        where 
            ( ParentType = @ParentType ) 
            and (ScopeID = @ParentID)
            and LOWER([Name]) = LOWER(@Name) 
            and ID <> IsNULL(@ID,0) )
        Begin
            Set @Return = -183  -- Same Name exist
        End

    End
    Else
    Begin
        Set @Return = -1
        Return (@Return)
    End
    
Return(@Return)

GO  

Create Procedure [AzMan_SP_Check_Dup_Group]
(
    @Return [int] output,
    @ParentID   [int],
    @ParentType [tinyint] ,
    @ID [int],
    @Name [nvarchar](64) 
)
AS

Declare @ParentStore [int]
Declare @ParentAppID [int]

Set @Return = 0
Set @ParentStore = 0
Set @ParentAppID = 0

    
    -- First Check the current Parent
    exec AzMan_SP_Check_Dup_Group_For_Given_Parent @Return output, @ParentID, @ParentType, @ID, @Name
    
    if  @Return <> 0 
    Begin
        Return(@Return)
    End

    -- Behave differently depending on the object type of the parent object
    --
    -- A group that is a child of the authorization store,
    --  cannot have the same name as any groups that are children of any of the child applications, and
    --  cannot have the same name as any groups that are children of any of the grandchild child scopes.

        if @ParentType = 0      -- Auth Store
        Begin

            if Exists( Select ID from 
                [dbo].[AzMan_AzApplicationGroup] 
            where 
                 ( ( ID <> IsNULL(@ID,0) ) and
                   ( [Name] = @Name )      and
                   (   (   ( ParentType = 1 ) and       -- Application
                           ( AppID in ( select ID from AzMan_AzApplication where StoreID = @ParentID ) )
                        )
                    or 
                        (  ( ParentType = 4 ) and   -- Scope
                           ( ScopeID in ( select ID from AzMan_AzScope 
                                            where AppID in (select ID from AzMan_AzApplication where StoreID = @ParentID) ) )
                        )
                    ) 
                 ) 
            )
            Begin
                Set @Return = -183  -- Same Name exist
            End
        End
        else if @ParentType = 1     -- Application
        Begin
        
            -- A group that is a child of an application
            --  cannot have the same name as groups that are children of the authorization store,
            --  and cannot have the same name as any groups that are children of any of the child scopes.
            
            -- Find the parent of Application 
            
            Select @ParentStore = [StoreID] 
            from AzMan_AzApplication
            where [ID] = @ParentID
            
            if ( @ParentStore = 0 )
            Begin
                Set @Return = -1 -- General Error
            End
            Else
            Begin
                -- and Check for conflict 
                exec AzMan_SP_Check_Dup_Group_For_Given_Parent @Return output, @ParentStore, 0, @ID, @Name
            End
            
            if ( @Return = 0 )
            Begin
                -- Find All the Child scopes of parent and check for conflicts
                
                if Exists( Select [Name] from 
                    [dbo].[AzMan_AzApplicationGroup] 
                where 
                    ( ParentType = 4 ) -- All Child scope
                      and 
                      LOWER([Name]) = LOWER(@Name)  and 
                     (@ParentID IN ( Select AppID from AzMan_AzScope where AppID = @ParentID and ID <> IsNULL(@ID,0)) ) )

                    
                Begin
                    Set @Return = -183  -- Same Name exist
                End
            End             
        End
        else if @ParentType = 4     -- Scope
        Begin
            -- A group that is a child of a scope,
            -- cannot have the same name as groups that are children of the application or authorization store
            
            -- Find the Parent App
            Select @ParentAppID = [AppID] 
            from AzMan_AzScope
            where [ID] = @ParentID
            
            if ( @ParentAppID = 0 )
            Begin
                Set @Return = -1 -- General Error
            End
            else
            Begin
                exec AzMan_SP_Check_Dup_Group_For_Given_Parent @Return output, @ParentAppID, 1, @ID, @Name
                
                if  @Return = 0 
                Begin
            
                    -- Find the Parent Store
                    Select @ParentStore = [StoreID] 
                    from AzMan_AzApplication
                    where [ID] = @ParentAppID
                    
                    if ( @ParentStore = 0 )
                    Begin
                        Set @Return = -1 -- General Error
                    End
                    else
                    Begin
                        exec AzMan_SP_Check_Dup_Group_For_Given_Parent @Return output, @ParentStore, 0, @ID, @Name
                    End
                End             
            End         
        End         

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzAuthorizationStore]
-- Inserts a new record in [AzMan_AzAuthorizationStore] table
(
  @Return       [int]   output  
, @ID [int] OUTPUT                            
, @ObjectGuid [uniqueidentifier] = Null     
, @DomainTimeout [int] = Null               
, @ScriptEngineTimeout [int] = Null         
, @MaxScriptEngines [int] = Null            
, @ApplyStoreSacl [bit] = Null              
, @GenerateAudits [bit] = 0                 
, @MajorVersion [int] = 1                   
, @MinorVersion [int] = 0                   
, @TargetMachine [nvarchar](50) = Null      
, @Description [nvarchar](1024) = Null      
, @Name [nvarchar] (512)        = NULL
, @ApplicationData [ntext] = Null           

) as 

    DECLARE @RowCount INT, @Error INT
    
    Set @Return = 0
    Set @ID     = 0 

    Set NoCount On
    
    Begin
        Insert Into [dbo].[AzMan_AzAuthorizationStore]
        (
              [ObjectGuid]
            , [Name]
            , [Description]
            , [ApplicationData]
            , [DomainTimeout]
            , [ScriptEngineTimeout]
            , [MaxScriptEngines]
            , [TargetMachine]
            , [ApplyStoreSacl]
            , [GenerateAudits]
            , [MajorVersion]
            , [MinorVersion]
        )

        Values
        (
             @ObjectGuid
            , @Name
            , @Description
            , @ApplicationData
            , @DomainTimeout
            , @ScriptEngineTimeout
            , @MaxScriptEngines
            , @TargetMachine
            , @ApplyStoreSacl
            , @GenerateAudits
            , @MajorVersion
            , @MinorVersion
        )

        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @RowCount = 1 
        Begin
            Set @ID = Cast(SCOPE_IDENTITY() As [int])
        End         
        else
        Begin
            Set @Return = @Error
        End
        
        -- generate a generic audit
        EXEC [AzMan_SP_GenerateGenericAudit]
                @Return,
                0,  -- 0 for store
                @Name,
                @ObjectGuid,
                N'Trying to create an AzAuthorizationStore'
                
        -- generate an audit

        EXEC [AzMan_SP_GenerateObjectAudit]
                @Return,
                0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
                0,  -- 0 for store
                @Name,
                @ObjectGuid,
                0,  -- 0 for store
                @Name,
                @ObjectGuid,
                N'' -- no other info
                
    End
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzApplication]

-- Inserts a new record in [AzMan_AzApplication] table
(
  @Return       [int]   output      
, @ID [int] = Null Output                   
, @ObjectGuid [uniqueidentifier] = Null     
, @StoreId [int]                    
, @ApplyStoreSacl [bit] = Null              
, @GenerateAudits [bit] = Null              
, @AuthzInterfaceClsId [int] = Null         
, @CheckDup [bit] = 1
, @ApplicationVersion [nvarchar](50) = Null 
, @Name [nvarchar](512) = Null              
, @Description [nvarchar](1024) = Null      
, @ApplicationData [ntext] = Null           
)

As

Set NoCount On

    Begin
    
    DECLARE @RowCount INT, @Error INT
    DECLARE @storeName [nvarchar](512)
    DECLARE @storeGuid [uniqueidentifier]
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    Set @Return = 0
    
        Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output, @StoreId, 0, 1, @SaclIsOn output
        
        -- Require Admin Access
        if @Return <> 1 
        Begin
            if (@Return >= 2)
            Begin
                Set @Return = -5 
            End
            goto Done
        End
    
        -- Check for duplicate name 
        IF @CheckDup = 1
            BEGIN
                Exec AzMan_SP_Check_Dup_Application @Return output, @StoreId, @ID, @Name
            END
        ELSE
            Set @Return = 0
            
        if @Return = 0 
        Begin

            Insert Into [dbo].[AzMan_AzApplication]
            (
                  [ObjectGuid]
                , [StoreID]
                , [Name]
                , [Description]
                , [ApplicationData]
                , [ApplyStoreSacl]
                , [GenerateAudits]
                , [AuthzInterfaceClsId]
                , [ApplicationVersion]
            )

            Values
            (
                  @ObjectGuid
                , @StoreId
                , @Name
                , @Description
                , @ApplicationData
                , @ApplyStoreSacl
                , @GenerateAudits
                , @AuthzInterfaceClsId
                , @ApplicationVersion
            )

            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount = 1
            Begin
                Set @ID = Cast(SCOPE_IDENTITY() As [int])
            End
            Else
            Begin
                Set @Return = @Error
            End
            
            -- generate an audit if SACL is on
            IF @SaclIsOn = 1
                BEGIN
                    -- get the store info
                    SELECT @storeName = Store.Name, @storeGuid = Store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] Store
                        WHERE Store.ID = @StoreId

                    -- generate an audit
                    EXEC [AzMan_SP_GenerateObjectAudit]
                        @Return,
                        0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
                        0,  -- 0 for store
                        @storeName,
                        @storeGuid,
                        1,  -- 1 for Application
                        @Name,
                        @ObjectGuid,
                        N'' -- no other info
               END
        End         
    End

Done:

Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPU_AzApplication]

-- Update an existing record in [AzMan_AzApplication] table

(
  @Return       [int]   output  
, @ID [int] 
, @StoreId [int]                    
, @ConsiderNull_Description bit = 0
, @ConsiderNull_ApplicationData bit = 0
, @ApplyStoreSacl [bit] = Null                      
, @ConsiderNull_ApplyStoreSacl bit = 0
, @GenerateAudits [bit] = Null                      
, @ConsiderNull_GenerateAudits bit = 0
, @AuthzInterfaceClsId [int] = Null                 
, @ConsiderNull_AuthzInterfaceClsId bit = 0
, @ConsiderNull_ApplicationVersion bit = 0
, @ApplicationVersion [nvarchar](50) = Null         
, @Name [nvarchar](512) 
, @Description [nvarchar](1024) = Null              
, @ApplicationData [ntext] = Null                   

)

As
DECLARE @ObjectGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 0, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5
        End
        goto Done
    End

Set @Return = 0

If @ConsiderNull_Description Is Null
    Set @ConsiderNull_Description = 0

If @ConsiderNull_ApplicationData Is Null
    Set @ConsiderNull_ApplicationData = 0

If @ConsiderNull_ApplyStoreSacl Is Null
    Set @ConsiderNull_ApplyStoreSacl = 0

If @ConsiderNull_GenerateAudits Is Null
    Set @ConsiderNull_GenerateAudits = 0

If @ConsiderNull_AuthzInterfaceClsId Is Null
    Set @ConsiderNull_AuthzInterfaceClsId = 0

If @ConsiderNull_ApplicationVersion Is Null
    Set @ConsiderNull_ApplicationVersion = 0


    IF @Name Is Not NULL
    Begin
        -- Check for duplicate name 
        Exec AzMan_SP_Check_Dup_Application @Return output, @StoreId, @ID, @Name
    End
    
    If @Return = 0 
    Begin 
        Update [dbo].[AzMan_AzApplication]

        Set
            [Name] = IsNull(@Name, [Name]) 
            ,[Description] = Case @ConsiderNull_Description When 0 Then IsNull(@Description, [Description]) When 1 Then @Description End
            ,[ApplicationData] = Case @ConsiderNull_ApplicationData When 0 Then IsNull(@ApplicationData, [ApplicationData]) When 1 Then @ApplicationData End
            ,[ApplyStoreSacl] = Case @ConsiderNull_ApplyStoreSacl When 0 Then IsNull(@ApplyStoreSacl, [ApplyStoreSacl]) When 1 Then @ApplyStoreSacl End
            ,[GenerateAudits] = Case @ConsiderNull_GenerateAudits When 0 Then IsNull(@GenerateAudits, [GenerateAudits]) When 1 Then @GenerateAudits End
            ,[AuthzInterfaceClsId] = Case @ConsiderNull_AuthzInterfaceClsId When 0 Then IsNull(@AuthzInterfaceClsId, [AuthzInterfaceClsId]) When 1 Then @AuthzInterfaceClsId End
            ,[ApplicationVersion] = Case @ConsiderNull_ApplicationVersion When 0 Then IsNull(@ApplicationVersion, [ApplicationVersion]) When 1 Then @ApplicationVersion End

        Where
            ([ID] = @ID)
    End              

    -- generate an audit if SACL is on
    IF @SaclIsOn = 1
        BEGIN
            -- get info for auditing
            SELECT @ObjectGuid = app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                WHERE app.ID = @ID

            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    1,  -- 0 for application
                    @Name,
                    @ObjectGuid,
                    N'The shallow properties of the application may have been modified'
        END
        
Done:
Set NoCount Off

Return(@Return)


GO

Create Procedure [AzMan_SPI_AzScope]

-- Inserts a new record in [AzMan_AzScope] table
(
  @Return       [int]   output  
, @ID [int] = Null      output
, @AppId [int] = Null                       
, @NameLen [int]         = 0                    
, @NameHash [Binary] (32) = NULL
, @ObjectGuid [uniqueidentifier] = Null         
, @CheckDup [bit] = 1
, @Description [nvarchar](1024) = Null          
, @Name [ntext]
, @ApplicationData [ntext] = Null               

)

As
DECLARE @appName nvarchar(512)
DECLARE @appGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set NoCount On

    Begin
    
    DECLARE @RowCount INT, @Error INT
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@AppId, 1, 1, @SaclIsOn output
    
    -- Require Admin Access at App
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
    
    IF @CheckDup = 1
        BEGIN
            Exec AzMan_SP_Check_Dup_Scope @Return output, @AppId, -1, @NameHash
        End
    ELSE
        Set @Return = 0
            
    if @Return = 0 
    Begin

        Insert Into [dbo].[AzMan_AzScope]
        (
              [AppID]
            , [Name]
            , [NameLen]
            , [NameHash]
            , [Description]
            , [ApplicationData]
            , [ObjectGuid]
        )

        Values
        (
            @AppId
            , @Name
            , @NameLen
            , @NameHash
            , @Description
            , @ApplicationData
            , @ObjectGuid
        )
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        If @RowCount = 1
        Begin
            Set @ID = Cast(SCOPE_IDENTITY() As [int])               
        End
        Else
        Begin
            Set @Return = @Error
        End
    End
    
    IF @SaclIsOn = 1
        BEGIN
            -- get the store info for auditing
            SELECT @appName = app.Name, @appGuid = app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                WHERE app.ID = @AppId

            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                @Return,
                0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
                1,  -- 1 for Application
                @appName,
                @appGuid,
                4,  -- 4 for scope
                @Name,
                @ObjectGuid,
                N'' -- no other info
        END
    End
Done:
Set NoCount Off

Return(@Return)


GO

Create Procedure [AzMan_SPI_AzOperation]

-- Inserts a new record in [AzMan_AzOperation] table
(
  @Return       [int]   output  
, @ID [int] = Null Output                       
, @OperationID [int] = Null                     
, @AppId [int] = Null                       
, @ObjectGuid [uniqueidentifier] = Null         
, @CheckDup [bit] = 1
, @Name [nvarchar](64) = Null                   
, @Description [nvarchar](1024) = Null          
, @ApplicationData [ntext] = Null               

)

As
DECLARE @appName nvarchar(512)
DECLARE @appGuid uniqueidentifier
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set NoCount On
    Begin

    DECLARE @RowCount INT, @Error INT   
    Declare @AccessAtObjType [tinyint]
    Set @Return = 0 
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output, @AppId, 1, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
    -- if check dup
    IF @CheckDup = 1
        BEGIN
            Exec AzMan_SP_Check_Dup_Operation @Return output, @AppId, @ID, @Name
        End
    ELSE
        Set @Return = 0
            
    if @Return = 0  
    Begin   
            
        Insert Into [dbo].[AzMan_AzOperation]
        (
              [ObjectGuid]
            , [AppID]
            , [Name]
            , [Description]
            , [ApplicationData]
            , [OperationID]
        )

        Values
        (
              @ObjectGuid
            , @AppId
            , @Name
            , @Description
            , @ApplicationData
            , @OperationID
        )

        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        If @RowCount = 1
        Begin
            Set @ID = Cast(SCOPE_IDENTITY() As [int])
        End
        Else
        Begin
            Set @Return = @Error
        End
        
    End
    
    IF @SaclIsOn = 1
        BEGIN
            -- get the store info for auditing
            SELECT @appName = app.Name, @appGuid = app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                WHERE app.ID = @AppId

            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                @Return,
                0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
                1,  -- 1 for Application
                @appName,
                @appGuid,
                2,  -- 2 for operation
                @Name,
                @ObjectGuid,
                N'' -- no other info
        END
    End
    
Done:
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzApplicationGroup]

-- Inserts a new record in [AzMan_AzApplicationGroup] table
(
  @Return       [int]   output  
, @ID [int] = Null Output 
, @ParentId [int] 
, @ParentType [int]  
, @GroupType [tinyint] = Null  
, @ObjectGuid [uniqueidentifier] = Null  
, @CheckDup [bit] = 1
, @Name [nvarchar](64) = Null  
, @Description [nvarchar](1024) = Null
)
As

    DECLARE @RowCount INT, @Error INT
    Declare @StoreID int, @AppID int , @ScopeID int
    
    -- parent name and guid is only needed for auditing
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier
    
    Declare @AccessAtObjType [tinyint]
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0

    Set @Return = 0
    
    set @StoreID = 0 
    set @AppID   = 0
    set @ScopeID = 0

    Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output, @ParentId, @ParentType, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    IF @CheckDup = 1
        BEGIN
            Exec AzMan_SP_Check_Dup_Group @Return output, @ParentId, @ParentType, @ID, @Name
        END
    ELSE
        Set @Return = 0
        
    if @Return = 0
    Begin

        if @ParentType = 0
        Begin
            set @StoreID = @ParentId
            -- get info for auditing            
            IF @SaclIsOn = 1
                BEGIN
                    SELECT @parentName = store.Name, @parentGuid = store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] store
                        WHERE store.ID = @ParentId
                END
        End
        else if @ParentType = 1
        Begin
            set @AppID = @ParentId 
            -- get info for auditing            
            IF @SaclIsOn = 1
                BEGIN
                    SELECT @parentName = app.Name, @parentGuid = app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                        WHERE app.ID = @ParentId 
                END
        End         
        else if @ParentType = 4
        Begin
            set @ScopeID = @ParentId 
            -- get info for auditing            
            IF @SaclIsOn = 1
                BEGIN
                    SELECT @parentName = scope.Name, @parentGuid = scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                        WHERE scope.ID = @ParentId
                END
        End         

        Insert Into [dbo].[AzMan_AzApplicationGroup]
        (
            [ObjectGuid]
            , [StoreID]
            , [AppID]           
            , [ScopeID]                     
            , [ParentType]
            , [Name]
            , [Description]
            , [GroupType]
        )

        Values
        (
            @ObjectGuid
            , @StoreID
            , @AppID            
            , @ScopeID                      
            , @ParentType
            , @Name
            , @Description
            , @GroupType
        )
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        If @RowCount = 1
        Begin
            Set @ID = Cast(SCOPE_IDENTITY() As [int])
        End
        Else
        Begin
            Set @Return = @Error
        End
    End         
    
    IF @SaclIsOn = 1
        BEGIN
        -- generate an audit
        EXEC [AzMan_SP_GenerateObjectAudit]
            @Return,
            0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
            @ParentType,  -- parent type
            @parentName,
            @parentGuid,
            5,  -- 5 for group
            @Name,
            @ObjectGuid,
            N'' -- no other info
        END

Done:

Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzTask]

-- Inserts a new record in [AzMan_AzTask] table
(
  @Return       [int]   output  
, @ID [int] = Null Output 
, @IsRoleDefinition [bit] = 0 
, @ParentId [int] 
, @ParentType [tinyint]  
, @ObjectGuid [uniqueidentifier] = Null  
, @CheckDup [bit] = 1
, @Name [nvarchar](64) = Null  
, @Description [nvarchar](1024) = Null  
, @ApplicationData [ntext] = Null   
)
As

DECLARE @RowCount INT, @Error INT
Declare @AppID [int], @ScopeID [int]
DECLARE @parentName nvarchar(512)
DECLARE @parentGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0
    
Set @Return = 0

Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentId, @ParentType, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    Set @AppID = null
    Set @ScopeID = null
    
    if @ParentType = 1
    Begin
        Set @AppID = @ParentId
        -- get info for auditing            
        IF @SaclIsOn = 1
            BEGIN
                SELECT @parentName = app.Name, @parentGuid = app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                    WHERE app.ID = @ParentId 
            END
    End
    Else if @ParentType = 4
    Begin 
        Set @ScopeID = @ParentId
        -- get info for auditing            
        IF @SaclIsOn = 1
            BEGIN
                SELECT @parentName = scope.Name, @parentGuid = scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                    WHERE scope.ID = @ParentId
            END
    End
    Else
    Begin
        Set @Return = -1
        goto Done
    End 

        -- Make sure that the name doesn't exist at the same level
        
        IF @CheckDup = 1
            BEGIN
                Exec AzMan_SP_Check_Dup_Task @Return output, @ParentId, @ParentType, @ID, @Name
            End
        ELSE
            Set @Return = 0
            
        if @Return = 0  
        Begin
            Insert Into [dbo].[AzMan_AzTask]
            (
                  [ObjectGuid]
                , [AppID]
                , [ScopeID]
                , [ParentType]
                , [Name]
                , [Description]
                , [ApplicationData]
                , [IsRoleDefinition]
            )

            Values
            (
                  @ObjectGuid
                , @AppID
                , @ScopeID
                , @ParentType
                , @Name
                , @Description
                , @ApplicationData
                , @IsRoleDefinition
            )
            
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount = 1
            Begin
                Set @ID = Cast(SCOPE_IDENTITY() As [int])
            End
            Else
            Begin
                Set @Return = @Error
            End
        End
        
    IF @SaclIsOn = 1
        -- generate an audit
        EXEC [AzMan_SP_GenerateObjectAudit]
            @Return,
            0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
            @ParentType,  -- parent type
            @parentName,
            @parentGuid,
            3,  -- 3 for task
            @Name,
            @ObjectGuid,
            N'' -- no other info

Done:  
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzTask_Single_Operation]
(
  @Return [int] output
, @TaskID       [int]               -- ID Of task
, @OperationID  [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @taskName nvarchar(512)
DECLARE @taskGuid uniqueidentifier
DECLARE @opName nvarchar(512)
DECLARE @opGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Task_To_Operation_Link] 
        (TaskID, OperationID) 
    VALUES 
        (@TaskID, @OperationID) 
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 --Generic Error
        End
    End
    
    IF @SaclIsOn = 1
        BEGIN
            -- get the info for auditing
            SELECT @taskName = Task.Name, @taskGuid = Task.ObjectGuid FROM [dbo].[AzMan_AzTask] Task
                WHERE Task.ID = @TaskID
            SELECT @opName = Op.Name, @opGuid = Op.ObjectGuid FROM [dbo].[AzMan_AzOperation] Op
                WHERE Op.ID = @OperationID
                        
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    2,  -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
                    3,  -- 3 for task
                    @taskName,
                    @taskGuid,
                    2,  -- 2 for Operation
                    @opName,
                    @opGuid,
                    N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzTask_Single_Operation]
(
  @Return [int] output
, @TaskID       [int]               -- ID Of task
, @OperationID  [int]
, @SaclIsOn     [bit]
)
As

Set @Return = 0

DECLARE @RowCount INT, @Error INT
DECLARE @taskName nvarchar(512)
DECLARE @taskGuid uniqueidentifier
DECLARE @opName nvarchar(512)
DECLARE @opGuid uniqueidentifier

Set NoCount On

    delete [AzMan_Task_To_Operation_Link] 
    where 
        TaskID = @TaskID and OperationID =  @OperationID 
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End
    End

    IF @SaclIsOn = 1
        BEGIN
            -- get the info for auditing
            SELECT @taskName = Task.Name, @taskGuid = Task.ObjectGuid FROM [dbo].[AzMan_AzTask] Task
                WHERE Task.ID = @TaskID
            SELECT @opName = Op.Name, @opGuid = Op.ObjectGuid FROM [dbo].[AzMan_AzOperation] Op
                WHERE Op.ID = @OperationID
                        
            -- generate an audit

            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    3,  -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
                    3,  -- 3 for task
                    @taskName,
                    @taskGuid,
                    2,  -- 2 for Operation
                    @opName,
                    @opGuid,
                    N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzTask_Multi_Operations]
(
  @Return [int] output
, @TaskID [int]             -- ID Of Task
, @SepChar [char] (1) = "|"
, @OperationIDs [nvarchar] (4000)
)
As

Set @Return = 0
DECLARE @equal char
DECLARE @addOrDelete nvarchar(10)
DECLARE @isAdd int
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

SET @equal = '='

Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@TaskID, 3, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

-- First parse the string
-- Insert each one of them 

DECLARE @OperationID varchar(100), @Pos int
Declare @iOperationID int

    SET @OperationIDs = LTRIM(RTRIM(@OperationIDs))+ @SepChar
    
    SET @Pos = CHARINDEX(@equal, @OperationIDs, 1)

    IF REPLACE(@OperationIDs, @SepChar, '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @OperationID = LTRIM(RTRIM(LEFT(@OperationIDs, @Pos - 1)))
            Set @iOperationID = CAST(@OperationID as int)
            
            -- remove the left part (the id)
            SET @OperationIDs = RIGHT(@OperationIDs, LEN(@OperationIDs) - @Pos)
            
            -- now move to the pipe separator
            SET @Pos = CHARINDEX(@SepChar, @OperationIDs, 1)
            SET @addOrDelete = LTRIM(RTRIM(LEFT(@OperationIDs, @Pos - 1)))
            
            Set @isAdd = CAST(@addOrDelete as int)
            
            IF @iOperationID <> 0
            BEGIN
                -- if NNNN=0, then it means to delete
                IF @isAdd <> 0
                BEGIN
                    Exec AzMan_SPI_AzTask_Single_Operation @Return output, @TaskID, @iOperationID, @SaclIsOn
                    if @Return <> 0 
                    Begin
                        Break   
                    End
                End
                ELSE
                BEGIN
                    Exec AzMan_SPD_AzTask_Single_Operation @Return output, @TaskID, @iOperationID, @SaclIsOn
                    if @Return <> 0 
                    Begin
                        Break   
                    End
                End
            END
            SET @OperationIDs = RIGHT(@OperationIDs, LEN(@OperationIDs) - @Pos)
            SET @Pos = CHARINDEX(@equal, @OperationIDs, 1)
        END
    END 

Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzRoleAssignment_Single_Operation]
(
  @Return [int] output
, @RoleID       [int]               -- ID Of Role
, @OperationID  [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier

DECLARE @opName nvarchar(512)
DECLARE @opGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Role_To_Operation_Link] 
        (RoleID, OperationID) 
    VALUES 
        (@RoleID, @OperationID) 
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 --Generic Error
        End 
    End
    
    IF @SaclIsOn = 1
        BEGIN
            -- get the info for auditing
            SELECT @roleName = Role.Name, @roleGuid = Role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] Role
                WHERE Role.ID = @RoleID
            SELECT @opName = Op.Name, @opGuid = Op.ObjectGuid FROM [dbo].[AzMan_AzOperation] Op
                WHERE Op.ID = @OperationID
                        
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    2,  -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    2,  -- 2 for Operation
                    @opName,
                    @opGuid,
                    N'' -- no other info
        END

Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzRoleAssignment_Single_Operation]
(
  @Return [int] output
, @RoleID       [int]               -- ID Of Role assignment
, @OperationID  [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier

DECLARE @opName nvarchar(512)
DECLARE @opGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    delete [AzMan_Role_To_Operation_Link] 
    where 
        RoleID = @RoleID and OperationID =  @OperationID 
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End
    End

    IF @SaclIsOn = 1
        BEGIN
            -- get the info for auditing
            SELECT @roleName = Role.Name, @roleGuid = Role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] Role
                    WHERE Role.ID = @RoleID
            SELECT @opName = Op.Name, @opGuid = Op.ObjectGuid FROM [dbo].[AzMan_AzOperation] Op
                    WHERE Op.ID = @OperationID
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    3,  -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    2,  -- 1 for Operation
                    @opName,
                    @opGuid,
                    N'' -- no other info
        END
        
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzRoleAssignment_Multi_Operations]
(
  @Return [int] output  
, @RoleID [int]             -- ID Of Role
, @SepChar [nchar] (1) = "|"
, @OperationIDs [nvarchar] (4000)
)
As

Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0
Set @Return = 0

Set NoCount On

-- First parse the string
-- Insert each one of them 

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleID, 6, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

DECLARE @OperationID nvarchar(100), @Pos int
Declare @iOperationID int
DECLARE @equal char
DECLARE @addOrDelete nvarchar(10)
DECLARE @isAdd int
SET @equal = '='

    SET @OperationIDs = LTRIM(RTRIM(@OperationIDs))+ @SepChar
    
    SET @Pos = CHARINDEX(@equal, @OperationIDs, 1)

    IF REPLACE(@OperationIDs, @SepChar, '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @OperationID = LTRIM(RTRIM(LEFT(@OperationIDs, @Pos - 1)))
            Set @iOperationID = CAST(@OperationID as int)
            
            -- remove the left part (the id)
            SET @OperationIDs = RIGHT(@OperationIDs, LEN(@OperationIDs) - @Pos)
            
            -- now move to the pipe separator
            SET @Pos = CHARINDEX(@SepChar, @OperationIDs, 1)
            SET @addOrDelete = LTRIM(RTRIM(LEFT(@OperationIDs, @Pos - 1)))
            
            Set @isAdd = CAST(@addOrDelete as int)
            
            IF @iOperationID <> 0
            BEGIN
                -- if NNNN=0, then it means to delete
                IF @isAdd <> 0
                BEGIN
                    Exec AzMan_SPI_AzRoleAssignment_Single_Operation @Return output, @RoleID, @iOperationID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End
                ELSE
                BEGIN
                    Exec AzMan_SPD_AzRoleAssignment_Single_Operation @Return output, @RoleID, @iOperationID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                END
            END
            
            SET @OperationIDs = RIGHT(@OperationIDs, LEN(@OperationIDs) - @Pos)
            SET @Pos = CHARINDEX(@equal, @OperationIDs, 1)
        END
    END 
    
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzTask_Single_Task]
(
  @Return [int] output
, @TaskID       [int]               
, @ChildID      [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @taskName nvarchar(512)
DECLARE @taskGuid uniqueidentifier
DECLARE @refTaskName nvarchar(512)
DECLARE @refTaskGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Task_To_Task_Link] 
        (TaskID, ChildID) 
    VALUES 
        (@TaskID, @ChildID) 
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 --Generic Error
        End 
    End
    
    -- get the info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @taskName = Task.Name, @taskGuid = Task.ObjectGuid FROM [dbo].[AzMan_AzTask] Task
                WHERE Task.ID = @TaskID
            SELECT @refTaskName = refTask.Name, @refTaskGuid = refTask.ObjectGuid FROM [dbo].[AzMan_AzTask] refTask
                WHERE refTask.ID = @ChildID
                        
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    2,  -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
                    3,  -- 3 for task
                    @taskName,
                    @taskGuid,
                    3,  -- 3 for task
                    @refTaskName,
                    @refTaskGuid,
                    N'' -- no other info
        END
        
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzTask_Single_Task]
(
  @Return [int] output,
  @TaskID       [int]               
, @ChildID      [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @taskName nvarchar(512)
DECLARE @taskGuid uniqueidentifier
DECLARE @refTaskName nvarchar(512)
DECLARE @refTaskGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    Delete From [AzMan_Task_To_Task_Link] 
    where TaskID = @TaskID and
          ChildID  = @ChildID 

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        Set @Return = @Error
    End

    -- get the info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @taskName = Task.Name, @taskGuid = Task.ObjectGuid FROM [dbo].[AzMan_AzTask] Task
                WHERE Task.ID = @TaskID
            SELECT @refTaskName = refTask.Name, @refTaskGuid = refTask.ObjectGuid FROM [dbo].[AzMan_AzTask] refTask
                WHERE refTask.ID = @ChildID
                        
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    3,  -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
                    3,  -- 3 for task
                    @taskName,
                    @taskGuid,
                    3,  -- 3 for task
                    @refTaskName,
                    @refTaskGuid,
                    N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzTask_Multi_Tasks]
(
  @Return [int] output
, @ParentTaskId [int]               -- ID Of Task
, @SepChar [char] (1) = "|"
, @TaskIDs [nvarchar] (4000)

)
As

Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set @Return = 0


Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentTaskId, 3, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End


-- First parse the string
-- Insert each one of them 

DECLARE @TaskID varchar(100), @Pos int
Declare @iTaskID int
DECLARE @equal char
DECLARE @addOrDelete nvarchar(10)
DECLARE @isAdd int
SET @equal = '='

    SET @TaskIDs = LTRIM(RTRIM(@TaskIDs))+ @SepChar
    
    SET @Pos = CHARINDEX(@equal, @TaskIDs, 1)

    --IF REPLACE(@TaskIDs, @SepChar, '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @TaskID = LTRIM(RTRIM(LEFT(@TaskIDs, @Pos - 1)))
            Set @iTaskID = CAST(@TaskID as int)
            
            -- remove the left part (the id)
            SET @TaskIDs = RIGHT(@TaskIDs, LEN(@TaskIDs) - @Pos)
            
            -- now move to the pipe separator
            SET @Pos = CHARINDEX(@SepChar, @TaskIDs, 1)
            SET @addOrDelete = LTRIM(RTRIM(LEFT(@TaskIDs, @Pos - 1)))
            
            Set @isAdd = CAST(@addOrDelete as int)
            
            
            IF @iTaskID <> 0
            BEGIN
                if @isAdd <> 0 
                Begin
                    Exec AzMan_SPI_AzTask_Single_Task @Return output, @ParentTaskId, @iTaskID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End                 
                else
                Begin
                    Exec AzMan_SPD_AzTask_Single_Task @Return output, @ParentTaskId, @iTaskID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End
                
            END
            SET @TaskIDs = RIGHT(@TaskIDs, LEN(@TaskIDs) - @Pos)
            SET @Pos = CHARINDEX(@equal, @TaskIDs, 1)
        END
    END 

Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzRoleAssignment_Single_Task]
(
  @Return [int] output,
  @RoleID       [int]               -- ID Of Role
, @TaskID       [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier
DECLARE @refTaskName nvarchar(512)
DECLARE @refTaskGuid uniqueidentifier

Set @Return = 0


Set NoCount On

    INSERT INTO [AzMan_Role_To_Task_Link] 
        (RoleID, TaskID) 
    VALUES 
        (@RoleID, @TaskID) 
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 --Generic Error
        End 
    End
        
    IF @SaclIsOn = 1
        BEGIN
            -- get the info for auditing
            SELECT @roleName = role.Name, @roleGuid = role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] role
                WHERE role.ID = @RoleID
            SELECT @refTaskName = refTask.Name, @refTaskGuid = refTask.ObjectGuid FROM [dbo].[AzMan_AzTask] refTask
                WHERE refTask.ID = @TaskID
                        
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    2,  -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    3,  -- 3 for task
                    @refTaskName,
                    @refTaskGuid,
                    N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure AzMan_SPD_AzRoleAssignment_Single_Task
(
  @Return [int] output,
  @RoleId [int]             -- ID Of Role Assignment
, @TaskID [int]
, @SaclIsOn     [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier
DECLARE @refTaskName nvarchar(512)
DECLARE @refTaskGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    Delete From [AzMan_Role_To_Task_Link] 
    where TaskID    = @TaskID and
          RoleID    = @RoleId
          
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End
    
    -- get the info for auditing
    SELECT @roleName = role.Name, @roleGuid = role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] role
        WHERE role.ID = @RoleId
    SELECT @refTaskName = refTask.Name, @refTaskGuid = refTask.ObjectGuid FROM [dbo].[AzMan_AzTask] refTask
        WHERE refTask.ID = @TaskID
    
    IF @SaclIsOn = 1
        BEGIN
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    3,  -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    3,  -- 3 for task
                    @refTaskName,
                    @refTaskGuid,
                    N'' -- no other info  
        END
Set NoCount Off

Return(@Return)

go


Create Procedure [AzMan_SPI_AzRoleAssignment_Multi_Tasks]
(
  @Return [int] output
, @RoleId [int]             
, @SepChar [char] (1) = "|"
, @TaskIDs [nvarchar] (4000)
)
As

Set NoCount On

Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set @Return = 0


    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleId, 6, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End


-- First parse the string
-- Insert each one of them 

DECLARE @TaskID varchar(100), @Pos int
Declare @iTaskID int
DECLARE @equal char
DECLARE @addOrDelete nvarchar(10)
DECLARE @isAdd int
SET @equal = '='


    SET @TaskIDs = LTRIM(RTRIM(@TaskIDs))+ @SepChar
    
    SET @Pos = CHARINDEX(@equal, @TaskIDs, 1)

    IF REPLACE(@TaskIDs, @SepChar, '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @TaskID = LTRIM(RTRIM(LEFT(@TaskIDs, @Pos - 1)))
            Set @iTaskID = CAST(@TaskID as int)
            

            -- remove the left part (the id)
            SET @TaskIDs = RIGHT(@TaskIDs, LEN(@TaskIDs) - @Pos)
            
            -- now move to the pipe separator
            SET @Pos = CHARINDEX(@SepChar, @TaskIDs, 1)
            SET @addOrDelete = LTRIM(RTRIM(LEFT(@TaskIDs, @Pos - 1)))
            
            Set @isAdd = CAST(@addOrDelete as int)
            
            
            IF @iTaskID <> 0
            BEGIN
                -- if NNNN=0, then it means to delete
                IF @isAdd <> 0
                Begin           
                    Exec [AzMan_SPI_AzRoleAssignment_Single_Task] @Return output, @RoleId, @iTaskID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End
                else
                Begin
                    Exec [AzMan_SPD_AzRoleAssignment_Single_Task] @Return output, @RoleId, @iTaskID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End                 
            END
            SET @TaskIDs = RIGHT(@TaskIDs, LEN(@TaskIDs) - @Pos)
            SET @Pos = CHARINDEX(@equal, @TaskIDs, 1)
        END
    END 

Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzRoleAssignment]

(
  @Return [int] output
, @ID [int] = Null Output 
, @ParentId [int] 
, @ParentType [tinyint]  
, @ObjectGuid [uniqueidentifier] = Null  
, @CheckDup [bit] = 1
, @Name [nvarchar](64) = Null  
, @Description [nvarchar](1024) = Null  
, @ApplicationData [ntext] = Null   
)
As

DECLARE @RowCount INT, @Error INT
Declare @AppId [int] , @ScopeID [int]
DECLARE @parentName nvarchar(512)
DECLARE @parentGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set @Return = 0

Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentId, @ParentType, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

        IF @CheckDup = 1
            BEGIN
                Exec AzMan_SP_Check_Dup_RoleAssignment @Return output, @ParentId, @ParentType, @ID, @Name
            END
        ELSE
            Set @Return = 0
            
        if @Return = 0 
        Begin 
        
            Set @AppId  = null
            Set @ScopeID= null 
            
            if @ParentType = 1
            Begin
                Set @AppId = @ParentId
                -- get info for auditing
                IF @SaclIsOn = 1
                    BEGIN
                        SELECT @parentName=app.Name, @parentGuid=app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                            WHERE app.ID = @ParentId
                    END
            End
            else if @ParentType = 4
            Begin
                Set @ScopeID = @ParentId
                -- get info for auditing
                IF @SaclIsOn = 1
                    BEGIN
                        SELECT @parentName=scope.Name, @parentGuid=scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                            WHERE scope.ID = @ParentId
                    END
            End
            else
            Begin
                Set @Return = -1
                Return
            End
    
            Insert Into [dbo].[AzMan_AzRoleAssignment]
            (
                  [ObjectGuid]
                , [AppID]
                , [ScopeID]
                , [ParentType]
                , [Name]
                , [Description]
                , [ApplicationData]
            )

            Values
            (
                  @ObjectGuid
                , @AppId
                , @ScopeID
                , @ParentType
                , @Name
                , @Description
                , @ApplicationData
            )

            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount = 1
            Begin

                Set @ID = Cast(SCOPE_IDENTITY() As [int])
            End
            Else
            Begin
                Set @Return = @Error
            End


        End         
        
    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    0,  -- 0 for SE_AUDITID_AZ_SQL_OBJECT_CREATE
                    @ParentType,
                    @parentName,
                    @parentGuid,
                    6,  -- 6 for role
                    @Name,
                    @ObjectGuid,
                    N'' -- no other info  
        END
Done:
          
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPU_AzAuthorizationStore]

-- Update an existing record in [AzMan_AzAuthorizationStore] table

(
  @Return [int] output
, @ID [int]
, @ConsiderNull_Description bit = 0
, @ConsiderNull_ApplicationData bit = 0
, @DomainTimeout [int] = Null                           
, @ConsiderNull_DomainTimeout bit = 0
, @ScriptEngineTimeout [int] = Null                     
, @ConsiderNull_ScriptEngineTimeout bit = 0
, @MaxScriptEngines [int] = Null                        
, @ConsiderNull_MaxScriptEngines bit = 0
, @ConsiderNull_TargetMachine bit = 0
, @ApplyStoreSacl [bit] = Null                          
, @ConsiderNull_ApplyStoreSacl bit = 0
, @GenerateAudits [bit] = Null                          
, @ConsiderNull_GenerateAudits bit = 0
, @MajorVersion [int] = Null                            
, @ConsiderNull_MajorVersion bit = 0
, @MinorVersion [int] = Null                            
, @ConsiderNull_MinorVersion bit = 0
, @TargetMachine [nvarchar](50) = Null                  
, @Description [nvarchar](1024) = Null                  
, @ApplicationData [ntext] = Null                       
)

As

DECLARE @RowCount INT, @Error INT
DECLARE @ObjectName nvarchar(512)
DECLARE @ObjectGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0
Set @Return = 0

Set NoCount On


    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 0, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

Set @Return = 0 -- no error

If @ConsiderNull_Description Is Null
    Set @ConsiderNull_Description = 0

If @ConsiderNull_ApplicationData Is Null
    Set @ConsiderNull_ApplicationData = 0

If @ConsiderNull_DomainTimeout Is Null
    Set @ConsiderNull_DomainTimeout = 0

If @ConsiderNull_ScriptEngineTimeout Is Null
    Set @ConsiderNull_ScriptEngineTimeout = 0

If @ConsiderNull_MaxScriptEngines Is Null
    Set @ConsiderNull_MaxScriptEngines = 0

If @ConsiderNull_TargetMachine Is Null
    Set @ConsiderNull_TargetMachine = 0

If @ConsiderNull_ApplyStoreSacl Is Null
    Set @ConsiderNull_ApplyStoreSacl = 0

If @ConsiderNull_GenerateAudits Is Null
    Set @ConsiderNull_GenerateAudits = 0

If @ConsiderNull_MajorVersion Is Null
    Set @ConsiderNull_MajorVersion = 0

If @ConsiderNull_MinorVersion Is Null
    Set @ConsiderNull_MinorVersion = 0

Update [dbo].[AzMan_AzAuthorizationStore]

Set
    [Description] = Case @ConsiderNull_Description When 0 Then IsNull(@Description, [Description]) When 1 Then @Description End
    ,[ApplicationData] = Case @ConsiderNull_ApplicationData When 0 Then IsNull(@ApplicationData, [ApplicationData]) When 1 Then @ApplicationData End
    ,[DomainTimeout] = Case @ConsiderNull_DomainTimeout When 0 Then IsNull(@DomainTimeout, [DomainTimeout]) When 1 Then @DomainTimeout End
    ,[ScriptEngineTimeout] = Case @ConsiderNull_ScriptEngineTimeout When 0 Then IsNull(@ScriptEngineTimeout, [ScriptEngineTimeout]) When 1 Then @ScriptEngineTimeout End
    ,[MaxScriptEngines] = Case @ConsiderNull_MaxScriptEngines When 0 Then IsNull(@MaxScriptEngines, [MaxScriptEngines]) When 1 Then @MaxScriptEngines End
    ,[TargetMachine] = Case @ConsiderNull_TargetMachine When 0 Then IsNull(@TargetMachine, [TargetMachine]) When 1 Then @TargetMachine End
    ,[ApplyStoreSacl] = Case @ConsiderNull_ApplyStoreSacl When 0 Then IsNull(@ApplyStoreSacl, [ApplyStoreSacl]) When 1 Then @ApplyStoreSacl End
    ,[GenerateAudits] = Case @ConsiderNull_GenerateAudits When 0 Then IsNull(@GenerateAudits, [GenerateAudits]) When 1 Then @GenerateAudits End
    ,[MajorVersion] = Case @ConsiderNull_MajorVersion When 0 Then IsNull(@MajorVersion, [MajorVersion]) When 1 Then @MajorVersion End
    ,[MinorVersion] = Case @ConsiderNull_MinorVersion When 0 Then IsNull(@MinorVersion, [MinorVersion]) When 1 Then @MinorVersion End

Where
         ([ID] = @ID) 
         

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        Set @Return = @Error
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ObjectName=store.Name, @ObjectGuid = store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] store
                WHERE store.ID = @ID
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    0,  -- 0 for store
                    @ObjectName,
                    @ObjectGuid,
                    N'The shallow properties of the store may have been modified'
        END
Done:
Set NoCount Off

Return(@Return)


GO

Create Procedure [AzMan_SPU_AzScope]

-- Update an existing record in [AzMan_AzScope] table

(
  @Return [int] output
,  @ID [int]                                    
, @AppId [int]              
, @ConsiderNull_Description bit = 0
, @ConsiderNull_ApplicationData bit = 0
, @NameHash [Binary] (32) = Null
, @Description [nvarchar](1024) = Null      
, @Name [ntext] = Null             
, @ApplicationData [ntext] = Null           

)

As
    Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @ObjectGuid uniqueidentifier
    DECLARE @iScopeNameLen int
    Declare @AccessAtObjType [tinyint]    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 4, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
    -- Only store or app admin can modify property of the scope object
    if ( (@AccessAtObjType <> 0) and  (@AccessAtObjType <> 1) )
    begin
        Set @Return = -5 
        goto Done
    end

    Set @Return = 0 

    If @ConsiderNull_Description Is Null
        Set @ConsiderNull_Description = 0

    If @ConsiderNull_ApplicationData Is Null
        Set @ConsiderNull_ApplicationData = 0

    if @Name Is NOT NULL 
    Begin
        Exec AzMan_SP_Check_Dup_Scope @Return output, @AppId, @ID, @NameHash
    End
    
    if @Return = 0 
    Begin               
        set @iScopeNameLen = DATALENGTH(@Name)
        
            Update [dbo].[AzMan_AzScope]
            Set
                 [Name] = IsNull(@Name, [Name]) 
                ,[NameHash] = IsNull(@NameHash, [NameHash]) 
                ,[Description] = Case @ConsiderNull_Description When 0 Then IsNull(@Description, [Description]) When 1 Then @Description End
                ,[ApplicationData] = Case @ConsiderNull_ApplicationData When 0 Then IsNull(@ApplicationData, [ApplicationData]) When 1 Then @ApplicationData End
            Where
                 ([ID] = @ID) 
                 
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount <> 1
            Begin
                Set @Return = @Error
            End

    End              
    
    IF @SaclIsOn = 1
        BEGIN
            -- get info for auditing
            SELECT @ObjectGuid = scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                WHERE scope.ID = @ID

            -- TODO Should we truncate the name for scopes?        
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    4,  -- 0 for scope
                    @Name,
                    @ObjectGuid,
                    N'The shallow properties of the scope may have been modified'
        END
Done:        
    Set NoCount Off

    Return(@Return)

GO

Create Procedure [AzMan_SPU_AzOperation]

-- Update an existing record in [AzMan_AzOperation] table

(
  @Return [int] output
, @ID [int]     
, @AppId [int]                          
, @ConsiderNull_Description bit = 0
, @ConsiderNull_ApplicationData bit = 0
, @OperationID [int] = Null                 
, @ConsiderNull_OperationID bit = 0
, @Name [nvarchar](64) = Null               
, @Description [nvarchar](1024) = Null      
, @ApplicationData [ntext] = Null           
)

As
    Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @ObjectGuid uniqueidentifier
    Declare @AccessAtObjType [tinyint]
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    Set @Return = 0 
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 2, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End 

    
    If @ConsiderNull_Description Is Null
        Set @ConsiderNull_Description = 0

    If @ConsiderNull_ApplicationData Is Null
        Set @ConsiderNull_ApplicationData = 0

    If @ConsiderNull_OperationID Is Null
        Set @ConsiderNull_OperationID = 0
    
    If @Name Is Not NULL
    Begin   
        Exec AzMan_SP_Check_Dup_Operation @Return output, @AppId, @ID, @Name
    End
    
    if @Return = 0  
    Begin   
        Update [dbo].[AzMan_AzOperation]
            Set
                [Name] = IsNull(@Name, [Name]) 
                ,[Description] = Case @ConsiderNull_Description When 0 Then IsNull(@Description, [Description]) When 1 Then @Description End
                ,[ApplicationData] = Case @ConsiderNull_ApplicationData When 0 Then IsNull(@ApplicationData, [ApplicationData]) When 1 Then @ApplicationData End
                ,[OperationID] = Case @ConsiderNull_OperationID When 0 Then IsNull(@OperationID, [OperationID]) When 1 Then @OperationID End

            Where
                       ([ID] = @ID)
                     
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        If @RowCount <> 1
        Begin
            Set @Return = @Error
        End
                 
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ObjectGuid = op.ObjectGuid FROM [dbo].[AzMan_AzOperation] op
                WHERE op.ID = @ID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    2,  -- 2 for operation
                    @Name,
                    @ObjectGuid,
                    N'The shallow properties of the operation may have been modified'
        END
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPU_AzApplicationGroup]

-- Update an existing record in [AzMan_AzApplicationGroup] table

(
  @Return [int] output
, @ID [int] 
, @ParentType [tinyint]
, @ParentID   [int]
, @GroupType [tinyint] = Null 
, @ConsiderNull_GroupType bit = 0
, @ConsiderNull_GroupDescription bit = 0
, @Name [nvarchar](64) = Null 
, @Description [nvarchar](1024) = Null 
)

As

DECLARE @RowCount INT, @Error INT
DECLARE @ObjectGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0
Set @Return = 0

Set NoCount On

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 5, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
If @ConsiderNull_GroupType Is Null
    Set @ConsiderNull_GroupType = 0
    
    if @Name IS NOT NULL    
    Begin
        Exec AzMan_SP_Check_Dup_Group @Return output, @ParentID, @ParentType, @ID, @Name
    End     
    
    if @Return = 0
    Begin

        Update [dbo].[AzMan_AzApplicationGroup]

        Set
            [Name] = IsNull(@Name, [Name]) 
            ,[GroupType] = 
                Case @ConsiderNull_GroupType 
                    When 0 Then IsNull(@GroupType, [GroupType]) 
                    When 1 Then @GroupType 
                End
            ,[Description] = 
                Case @ConsiderNull_GroupDescription 
                    When 0 Then IsNull(@Description, [Description]) 
                    When 1 Then @Description 
                End
        Where
                 ([ID] = @ID)
                 
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount <> 1
            Begin
                Set @Return = @Error
            End
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ObjectGuid = appGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                WHERE appGroup.ID = @ID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    5,  -- 5 for application group
                    @Name,
                    @ObjectGuid,
                    N'The shallow properties of the application group may have been modified'
        END
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPU_AzTask]

-- Update an existing record in  table

(
  @Return [int] output
, @ID [int] 
, @ParentID   [int]
, @ParentType [tinyint]
, @ConsiderNull_Description bit = 0
, @IsRoleDefinition [bit] = Null 
, @ConsiderNull_IsRoleDefinition bit = NULL
, @ConsiderNull_ApplicationData bit = NULL
, @Name [nvarchar](64) = Null 
, @Description [nvarchar](1024) = Null 
, @ApplicationData [ntext] = Null           
)

As
    DECLARE @ObjectGuid uniqueidentifier
    Set NoCount On
    
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 3, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    Set @Return = 0

    If @ConsiderNull_Description Is Null
        Set @ConsiderNull_Description = 0

    if @ConsiderNull_IsRoleDefinition is NULL
        Set @ConsiderNull_IsRoleDefinition = 0
        
    If @ConsiderNull_ApplicationData Is Null
        Set @ConsiderNull_ApplicationData = 0

    if @Name Is Not NULL
    Begin
        Exec AzMan_SP_Check_Dup_Task @Return output, @ParentID, @ParentType, @ID, @Name
    End     
    
    if @Return = 0      
    Begin 
        Update [dbo].[AzMan_AzTask]

        Set
             [Name] = IsNull(@Name, [Name]) 
            ,[Description] = Case @ConsiderNull_Description When 0 Then IsNull(@Description, [Description]) When 1 Then @Description End
            ,[IsRoleDefinition] = Case @ConsiderNull_IsRoleDefinition When 0 Then IsNull(@IsRoleDefinition, [IsRoleDefinition]) When 1 Then @IsRoleDefinition End
            ,[ApplicationData] = Case @ConsiderNull_ApplicationData When 0 Then IsNull(@ApplicationData, [ApplicationData]) When 1 Then @ApplicationData End

        Where
                 ([ID] = @ID)
                 
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        If @RowCount <> 1
        Begin
            Set @Return = @Error
        End
                 
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ObjectGuid = task.ObjectGuid FROM [dbo].[AzMan_AzTask] task
                WHERE task.ID = @ID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    3,  -- 3 for task
                    @Name,
                    @ObjectGuid,
                    N'The shallow properties of the task may have been modified'
        END
Done:
    Set NoCount Off

    Return(@Return)
    
GO

Create Procedure [AzMan_SPU_AzRoleAssignment]

-- Update an existing record in the table

(
  @Return [int] output
, @ID [int] 
, @ParentID   [int]
, @ParentType [tinyint]
, @ConsiderNull_Description bit = 0
, @ConsiderNull_ApplicationData bit = NULL
, @Name [nvarchar](64) = Null 
, @Description [nvarchar](1024) = Null 
, @ApplicationData [ntext] = Null
)

As

Set NoCount On

DECLARE @RowCount INT, @Error INT
DECLARE @ObjectGuid uniqueidentifier
Declare @AccessAtObjType [tinyint]
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 6, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    Set @Return = 0

    If @ConsiderNull_Description Is Null
        Set @ConsiderNull_Description = 0
        
    If @ConsiderNull_ApplicationData Is Null
        Set @ConsiderNull_ApplicationData = 0
        

    IF @Name Is Not NULL
    Begin
            Exec AzMan_SP_Check_Dup_RoleAssignment @Return output, @ParentID, @ParentType, @ID, @Name
    End

    if @Return = 0
    Begin
        Update [dbo].[AzMan_AzRoleAssignment]

        Set
             [Name] = IsNull(@Name, [Name]) 
            ,[Description] = Case @ConsiderNull_Description When 0 Then IsNull(@Description, [Description]) When 1 Then @Description End
            ,[ApplicationData] = Case @ConsiderNull_ApplicationData When 0 Then IsNull(@ApplicationData, [ApplicationData]) When 1 Then @ApplicationData End            
        Where
                 ([ID] = @ID)
                 
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        If @RowCount <> 1
        Begin
            Set @Return = @Error
        End
                 
    End              
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ObjectGuid = role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] role
                WHERE role.ID = @ID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    6,  -- 6 for role
                    @Name,
                    @ObjectGuid,
                    N'The shallow properties of the role assignment may have been modified'
        END
Done:
Set NoCount Off

Return(@Return)

GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SPIU_AzApplicationGroup_LDAPQuery' and type = 'P')
    DROP PROCEDURE AzMan_SPIU_AzApplicationGroup_LDAPQuery
GO

Create Procedure [AzMan_SPIU_AzApplicationGroup_LDAPQuery]

-- Inserts or updates a record in [AzMan_LDAPQuery] table
(
  @Return [int] output
, @GroupId [int] = Null     -- ID Of Application Group
, @ConsiderNull_LdapQuery bit = 0
, @LdapQuery [ntext] = Null -- LDAP Query
)

As

Set NoCount On

DECLARE @RowCount INT, @Error INT
DECLARE @Name nvarchar(512)
DECLARE @ID [int] 
DECLARE @ObjectGuid uniqueidentifier
Declare @ExistingLdapQueryID [int]
Declare @AccessAtObjType [tinyint]
Set @ExistingLdapQueryID = Null
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0
    
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupId, 5, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

Set @Return = 0

Select @ExistingLdapQueryID = [LdapQueryID] From [dbo].[AzMan_AzApplicationGroup] Where [ID] = @GroupId  
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

if @Error=0 
Begin 
  if @RowCount = 0
  Begin
  -- Parent Group was not found
  Set @Return = 1
  Return(1)
  End
End
else
Begin
  Return @Error
End

If @ExistingLdapQueryID Is Null
    Begin
        Begin
    
            Insert Into [dbo].[AzMan_LDAPQuery]
            (
                  [GroupID]
                , [LdapQuery]
            )
    
            Values
            (
                  @GroupId
                , @LdapQuery
            )
            
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount = 1
            Begin

                Set @ID = Cast(SCOPE_IDENTITY() As [int])
                
                -- Update the AzApplication Groups with the new ID
                if @ID <> 0
                Begin
                    Update [dbo].[AzMan_AzApplicationGroup] 
                        Set [LdapQueryID] = @ID
                    where [ID] = @GroupId                   
                End             
            End
            Else
            Begin
                Set @Return = @Error
            End
        End
    End
Else
    Begin

        If @ConsiderNull_LdapQuery Is Null
            Set @ConsiderNull_LdapQuery = 0

        Update [dbo].[AzMan_LDAPQuery]
        Set
          [GroupID] = @GroupId
        ,[LdapQuery] = Case @ConsiderNull_LdapQuery When 0 Then IsNull(@LdapQuery, [LdapQuery]) When 1 Then @LdapQuery End
        Where
            [ID] = @ExistingLdapQueryID

        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        
        if @RowCount <> 1 
            Set @ID = @ExistingLdapQueryID
        else
        Begin
            Set @Return = @Error
        End
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @Name=appGroup.Name, @ObjectGuid = appGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                WHERE appGroup.ID = @GroupId
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    5,  -- 5 for group
                    @Name,
                    @ObjectGuid,
                    N'The LDAP query of the application may have been modified'
        END
Done:
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPIU_Bizrule]
(
  @Return [int] output,
  @ParentId [int] 
, @ParentType [tinyint] 
, @BizRuleImportedPath [nvarchar](512) = Null   -- for [AzMan_BizRule].[BizRuleImportedPath] column
, @ConsiderNull_BizRuleImportedPath bit = 0
, @BizRule [ntext] = Null                       -- for [AzMan_BizRule].[BizRule] column
, @ConsiderNull_BizRule bit = 0
, @BizRuleLanguage [nvarchar](64) = Null        -- for [AzMan_BizRule].[BizRuleLanguage] column
, @ConsiderNull_BizRuleLanguage bit = 0
)

As
DECLARE @RowCount INT, @Error INT
DECLARE @parentName nvarchar(512)
DECLARE @parentGuid uniqueidentifier
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set @Return = 0

Set NoCount On

Declare @ExistingBizruleID [int]
Declare @AccessAtObjType [tinyint]
Set @ExistingBizruleID = Null

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentId, @ParentType, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End


Set @Return = 0

if @ParentType = 3 -- task
Begin
    Select @ExistingBizruleID = [BizRuleID] 
    From [dbo].[AzMan_BizRule_To_Task]
    Where [TaskID] = @ParentId  
End

else if @ParentType = 5 -- group
Begin
    Select @ExistingBizruleID = [BizRuleID] 
    From [dbo].[AzMan_BizRule_To_Group]
    Where [GroupID] = @ParentId  
End
    
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

If @ExistingBizruleID Is Null or @ExistingBizruleID = 0 
    Begin
        Begin
    
            Insert Into [dbo].[AzMan_BizRule]
            (
                  [ParentId]
                , [ParentType]
                , [BizRuleImportedPath] 
                , [BizRule] 
                , [BizRuleLanguage]
            )
    
            Values
            (
                  @ParentId
                 ,@ParentType
                 ,@BizRuleImportedPath 
                 ,@BizRule 
                 ,@BizRuleLanguage
            )

            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            If @RowCount = 1
            Begin
                Set @ExistingBizruleID = Cast(SCOPE_IDENTITY() As [int])
                
                -- Update the AzApplication Groups or Task with the new ID
            
                if @ExistingBizruleID <> 0
                
                    if @ParentType = 3 -- task
                    Begin
                        insert [dbo].[AzMan_BizRule_To_Task] 
                        (
                            [TaskID] ,
                            [BizRuleID]
                        )
                        Values
                        (
                            @ParentId ,
                            @ExistingBizruleID
                        )
                    End

                    else if @ParentType = 5 -- group
                    Begin
                        insert [dbo].[AzMan_BizRule_To_Group] 
                        (
                            [GroupID] ,
                            [BizRuleID]
                        )
                        Values
                        (
                            @ParentId ,
                            @ExistingBizruleID
                        )
                    End
            End         
            Else
            Begin
                Set @Return = @Error
            End
                    
        End
    End
Else
    Begin

        If @ConsiderNull_BizRuleImportedPath Is Null
            set @ConsiderNull_BizRuleImportedPath = 0 
            
        If @ConsiderNull_BizRule Is Null
            set @ConsiderNull_BizRule = 0
            
        If @ConsiderNull_BizRuleLanguage Is Null
            set @ConsiderNull_BizRuleLanguage = 0 

        Update [dbo].[AzMan_BizRule]
        Set
          [ParentId]   = @ParentId
        , [ParentType] = @ParentType
        , [BizRuleImportedPath] = Case @ConsiderNull_BizRuleImportedPath When 0 Then IsNull(@BizRuleImportedPath, [BizRuleImportedPath]) When 1 Then @BizRuleImportedPath End
        , [BizRule] = Case @ConsiderNull_BizRule When 0 Then IsNull(@BizRule, [BizRule]) When 1 Then @BizRule End
        , [BizRuleLanguage] = Case @ConsiderNull_BizRuleLanguage When 0 Then IsNull(@BizRuleLanguage, [BizRuleLanguage]) When 1 Then @BizRuleLanguage End

        Where
            [ID] = @ExistingBizruleID
            
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
                            
        if @RowCount = 0 
        Begin
            if @Error = 0
            Begin
                Set @Return = -1168 -- Error updating the Record
            End
            Else
            Begin
                Set @Return = @Error 
            End
            
        End
    End
    
    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            -- get info for auditing
            IF @ParentType = 3 -- task
                BEGIN
                    SELECT @parentName=task.Name, @parentGuid = task.ObjectGuid 
                        FROM [dbo].[AzMan_AzTask] task WHERE task.ID = @ParentId
                END
            ELSE IF @ParentType = 5 -- app group
                BEGIN
                    SELECT @parentName=appGroup.Name, @parentGuid = appGroup.ObjectGuid 
                        FROM [dbo].[AzMan_AzApplicationGroup] appGroup WHERE appGroup.ID = @ParentId
                END
            
            EXEC [AzMan_SP_GenerateGenericAudit]
                    @Return,
                    @ParentType,
                    @parentName,
                    @parentGuid,
                    N'The bizrule of the object may have been modified'
        END
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure dbo.[AzMan_SPIU_AzApplicationGroup_Bizrule]
(
  @Return [int] output
, @GroupId [int] 
, @ConsiderNull_BizRuleImportedPath bit = 0
, @ConsiderNull_BizRule bit = 0
, @ConsiderNull_BizRuleLanguage bit = 0
, @BizRuleLanguage [nvarchar](64) = Null        -- for [AzMan_BizRule].[BizRuleLanguage] column
, @BizRuleImportedPath [nvarchar](512) = Null   -- for [AzMan_BizRule].[BizRuleImportedPath] column
, @BizRule [ntext] = Null                       -- for [AzMan_BizRule].[BizRule] column

)
As

    Set @Return = 0
    exec AzMan_SPIU_Bizrule   @Return output, @GroupId, 5
            , @BizRuleImportedPath 
            , @ConsiderNull_BizRuleImportedPath 
            , @BizRule 
            , @ConsiderNull_BizRule 
            , @BizRuleLanguage 
            , @ConsiderNull_BizRuleLanguage 

Return @Return

Go

Create Procedure dbo.[AzMan_SPIU_AzTask_Bizrule]
(
  @Return [int] output
, @TaskId [int] 
, @ConsiderNull_BizRuleImportedPath bit = 0
, @ConsiderNull_BizRule bit = 0
, @ConsiderNull_BizRuleLanguage bit = 0
, @BizRuleLanguage [nvarchar](64) = Null        -- for [AzMan_BizRule].[BizRuleLanguage] column
, @BizRuleImportedPath [nvarchar](512) = Null   -- for [AzMan_BizRule].[BizRuleImportedPath] column
, @BizRule [ntext] = Null                       -- for [AzMan_BizRule].[BizRule] column

)
As

Set @Return = 0
    exec AzMan_SPIU_Bizrule   @Return output, @TaskId,  3
            , @BizRuleImportedPath 
            , @ConsiderNull_BizRuleImportedPath 
            , @BizRule 
            , @ConsiderNull_BizRule 
            , @BizRuleLanguage 
            , @ConsiderNull_BizRuleLanguage 

Return @Return

Go


Create Procedure [AzMan_SPI_AzApplicationGroup_Single_SidMember]

(
  @Return [int] output,
  @GroupId [int]            -- ID Of Application Group
, @IsMember [bit]           = 0 
, @SidMember varbinary(85)
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @parentName nvarchar(512)
DECLARE @parentGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Group_SIDMember] 
        (MemberSID, Member, GroupID) 
    VALUES 
        (@SidMember, @IsMember, @GroupId)
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 --Generic Error
        End 
    End
    
    -- get the info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @parentName = appGroup.Name, @parentGuid = appGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                WHERE appGroup.ID = @GroupId

            -- generate an audit
            IF @IsMember = 1
                EXEC [AzMan_SP_GenerateMemberAudit]
                        @Return,
                        4,  -- 4 for SE_AUDITID_AZ_SQL_MEMBER_ASSIGN
                        5,  -- 5 for group
                        @parentName,
                        @parentGuid,
                        N'',
                        @SidMember,
                        1,  -- 1 for member
                        N'' -- no other info
            ELSE
                EXEC [AzMan_SP_GenerateMemberAudit]
                        @Return,
                        4,  -- 4 for SE_AUDITID_AZ_SQL_MEMBER_ASSIGN
                        5,  -- 5 for group
                        @parentName,
                        @parentGuid,
                        N'',
                        @SidMember,
                        0,  -- 0 for non-member
                        N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzApplicationGroup_Single_SidMember]

(
  @Return [int] output,
  @GroupId [int]            -- ID Of Application Group
, @IsMember [bit] = 1 
, @SidMember varbinary(85)
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @parentName nvarchar(512)
DECLARE @parentGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    Delete From [AzMan_Group_SIDMember] 
    where MemberSID    = @SidMember and
          Member   = @IsMember and
          GroupID = @GroupId

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --REcord not found
        End 
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @parentName = appGroup.Name, @parentGuid = appGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                WHERE appGroup.ID = @GroupId

            -- generate an audit
            IF @IsMember = 1
                EXEC [AzMan_SP_GenerateMemberAudit]
                        @Return,
                        5,  -- 5 for SE_AUDITID_AZ_SQL_MEMBER_REMOVE
                        5,  -- 5 for group
                        @parentName,
                        @parentGuid,
                        N'',
                        @SidMember,
                        1,  -- 1 for member
                        N'' -- no other info
            ELSE
                EXEC [AzMan_SP_GenerateMemberAudit]
                        @Return,
                        5,  -- 5 for SE_AUDITID_AZ_SQL_MEMBER_REMOVE
                        5,  -- 5 for group
                        @parentName,
                        @parentGuid,
                        N'',
                        @SidMember,
                        0,  -- 0 for non-member
                        N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzApplicationGroup_Multi_SidMembers_Internal]
(
  @Return [int] output
, @GroupId [int]            -- ID Of Application Group
, @IsMember [bit] = 1
, @SidMembers varbinary (4000)
)
As
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set NoCount On

-- SidMembers are aggregated binary data in the following format:
--   The first 4 bytes is a delete/add flag (0/1); the following 4 bytes is the size of the SID, 
--   followed by the actual SID. The over-usage of these pieces of data is for ease of parsing
--   Using string to represent such encoding, the following example which encodes 3 SIDs 
--   of length 24, 36, and 48 in turn, and the second one is a delete
--   00010024NNNNNNNNN00000036NNNNNNNNNN00010048NNNNNNNNNNNNNN
-- Insert each one of them 

    Declare @AccessAtObjType [tinyint]
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupId, 5, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

DECLARE @DataLength int
DECLARE @SidLength int
DECLARE @SidMember varbinary(85)
DECLARE @Pos int
DECLARE @isAdd int
SET @DataLength = DATALENGTH(@SidMembers)

Set @Return = 0
Set @Pos = 1

WHILE @DataLength - @Pos > 8
    BEGIN
        SET @isAdd = CAST(SUBSTRING(@SidMembers, @Pos, 1) AS int)
        SET @Pos = @Pos + 1
        
        SET @SidLength = CAST(SUBSTRING(@SidMembers, @Pos, 1) AS int)
        SET @Pos = @Pos + 1

        -- make sure that we the SidLength is not lying to us!
        IF @SidLength >= 12 AND @SidLength < 85 AND @DataLength - @Pos >= @SidLength - 1
            BEGIN
                SET @SidMember = CAST(SUBSTRING(@SidMembers, @Pos, @SidLength) AS varbinary)
                SET @Pos = @Pos + @SidLength        

                -- if NNNN=0, then it means to delete
                IF @isAdd <> 0
                    BEGIN
                        Exec AzMan_SPI_AzApplicationGroup_Single_SidMember @Return output, @GroupId, @IsMember, @SidMember, @SaclIsOn
                        IF @Return <> 0
                            Break
                    End 
                ELSE
                    BEGIN
                        Exec AzMan_SPD_AzApplicationGroup_Single_SidMember @Return output, @GroupId, @IsMember ,@SidMember, @SaclIsOn
                        IF @Return <> 0
                            Break
                    End
            END
        ELSE
            SET @Pos = @DataLength
    END

Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzApplicationGroup_Multi_SidMembers]

(
  @Return [int] output
, @GroupId [int]            -- ID Of Application Group
, @SidMembers varbinary (4000)
)
As
    Exec AzMan_SPI_AzApplicationGroup_Multi_SidMembers_Internal @Return output, @GroupId, 1 , @SidMembers
    Return(@Return)
go


Create Procedure AzMan_SPI_AzApplicationGroup_Multi_SidNonMembers

(
  @Return [int] output ,    
  @GroupId [int]            -- ID Of Application Group
, @SidMembers varbinary (4000)
)
As
    Exec AzMan_SPI_AzApplicationGroup_Multi_SidMembers_Internal @Return output, @GroupId, 0 , @SidMembers
    Return(@Return)
go


Create Procedure [AzMan_SPI_AzApplicationGroup_Single_AppMember]
(
  @Return [int] output,
  @GroupId [int]            -- ID Of Application Group
, @ChildID [int] 
, @IsMember [bit]           = 1 
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @groupName nvarchar(512)
DECLARE @groupGuid uniqueidentifier
DECLARE @refGroupName nvarchar(512)
DECLARE @refGroupGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Group_AppMember] 
        (ChildID, Member, GroupID) 
    VALUES 
        (@ChildID, @IsMember, @GroupId)
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 --Generic Error
        End 
    End
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @groupName = appGroup.Name, @groupGuid = appGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                WHERE appGroup.ID = @GroupId
            SELECT @refGroupName = refGroup.Name, @refGroupGuid = refGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] refGroup
                WHERE refGroup.ID = @ChildID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    2,  -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
                    5,  -- 5 for application group
                    @groupName,
                    @groupGuid,
                    5,  -- 5 for application group
                    @refGroupName,
                    @refGroupGuid,
                    N'' -- no other info
        END
Set NoCount Off

Return @Return

GO

Create Procedure [AzMan_SPD_AzApplicationGroup_Single_AppMember]

(
  @Return [int] output,
  @GroupId [int]            -- ID Of Application Group
, @ChildID [int] 
, @IsMember [bit] = 1 
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @groupName nvarchar(512)
DECLARE @groupGuid uniqueidentifier
DECLARE @refGroupName nvarchar(512)
DECLARE @refGroupGuid uniqueidentifier
Set @Return = 0

Set NoCount On

    Delete From [AzMan_Group_AppMember] 
    where ChildID    = @ChildID and
          Member   = @IsMember and
          GroupID = @GroupId
          
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @groupName = appGroup.Name, @groupGuid = appGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                WHERE appGroup.ID = @GroupId
            SELECT @refGroupName = refGroup.Name, @refGroupGuid = refGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] refGroup
                WHERE refGroup.ID = @ChildID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    3,  -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
                    5,  -- 5 for application group
                    @groupName,
                    @groupGuid,
                    5,  -- 5 for application group
                    @refGroupName,
                    @refGroupGuid,
                    N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzApplicationGroup_Multi_AppMembers_Internal]

(
  @Return [int] output,
  @GroupId [int]            -- ID Of Application Group
, @SepChar [char] (1) = "|"
, @IsMember [bit] = 1 
, @AppMemberIDs [nvarchar] (4000)
)
As
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set NoCount On

-- First parse the SidMembers string
-- Insert each one of them 

    Declare @AccessAtObjType [tinyint]
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupId, 5, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End


DECLARE @AppMember varchar(100), @Pos int
DECLARE @iAppMemberID int
DECLARE @equal char
DECLARE @addOrDelete nvarchar(10)
DECLARE @isAdd int
SET @equal = '='


    Set @Return = 0

    SET @AppMemberIDs = LTRIM(RTRIM(@AppMemberIDs))+ @SepChar
    
    SET @Pos = CHARINDEX(@equal, @AppMemberIDs, 1)

    IF REPLACE(@AppMemberIDs, @SepChar, '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @AppMember = LTRIM(RTRIM(LEFT(@AppMemberIDs, @Pos - 1)))
            Set @iAppMemberID = CAST(@AppMember as int)
            
            -- remove the left part (the id)
            SET @AppMemberIDs = RIGHT(@AppMemberIDs, LEN(@AppMemberIDs) - @Pos)
            
            -- now move to the pipe separator
            SET @Pos = CHARINDEX(@SepChar, @AppMemberIDs, 1)
            SET @addOrDelete = LTRIM(RTRIM(LEFT(@AppMemberIDs, @Pos - 1)))
            
            Set @isAdd = CAST(@addOrDelete as int)
            
            
            IF @iAppMemberID > 0
            BEGIN
                
                -- if NNNN=0, then it means to delete
                IF @isAdd <> 0
                BEGIN
                    Exec AzMan_SPI_AzApplicationGroup_Single_AppMember @Return output, @GroupId, @iAppMemberID , @IsMember, @SaclIsOn

                    if @Return <> 0
                    Begin
                        Break
                    End
                End                 
                else
                BEGIN
                    Exec AzMan_SPD_AzApplicationGroup_Single_AppMember @Return output, @GroupId, @iAppMemberID , @IsMember, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End                 
                
            END
            SET @AppMemberIDs = RIGHT(@AppMemberIDs, LEN(@AppMemberIDs) - @Pos)
            SET @Pos = CHARINDEX(@equal, @AppMemberIDs, 1)
        END
    END 

Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPI_AzApplicationGroup_Multi_AppMembers]

(
  @Return [int] output
, @GroupId [int]            -- ID Of Application Group
, @SepChar [char] (1) = "|"
, @AppMemberIDs [nvarchar] (4000)
)
As

    Exec AzMan_SPI_AzApplicationGroup_Multi_AppMembers_Internal @Return output, @GroupId, @SepChar, 1 , @AppMemberIDs

    Return(@Return)
go

Create Procedure [AzMan_SPI_AzApplicationGroup_Multi_AppNonMembers]
(
  @Return [int] output
, @GroupId [int]            -- ID Of Application Group
, @SepChar [char] (1) = "|"
, @AppMemberIDs [nvarchar] (4000)
)
As

    Exec AzMan_SPI_AzApplicationGroup_Multi_AppMembers_Internal @Return output, @GroupId, @SepChar, 0 , @AppMemberIDs

    Return(@Return)
go


Create Procedure [AzMan_SPI_AzRoleAssignment_Single_SidMember]
(
  @Return [int] output,
  @RoleId [int]             -- ID Of Role
, @SidMember varbinary (85)
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Role_SIDMember] 
        (MemberSID,  RoleID) 
    VALUES 
        (@SidMember,@RoleId)
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount = 0
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 -- General Error
        End
    End
    
    -- get the info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @roleName = Role.Name, @roleGuid = Role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] Role
                WHERE Role.ID = @RoleId

            -- generate an audit
            EXEC [AzMan_SP_GenerateMemberAudit]
                    @Return,
                    4,  -- 4 for SE_AUDITID_AZ_SQL_MEMBER_ASSIGN
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    N'',
                    @SidMember,
                    1,  -- 1 for member
                    N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzRoleAssignment_Single_SidMember]
(
  @Return [int] output,
  @RoleId [int]             -- ID Of RoleAssignment
, @SidMember varbinary(85)
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier
DECLARE @userName nvarchar(512)

    Set NoCount On
    Set @Return = 0

    Delete From [AzMan_Role_SIDMember] 
    where MemberSID    = @SidMember and
          RoleID = @RoleId
          
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End
    End
    
    -- get the info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @roleName = Role.Name, @roleGuid = Role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] Role
                WHERE Role.ID = @RoleId
                
            -- We can't lookup names from SID, but XP can do that
            SET @userName = ''

            -- generate an audit
            EXEC [AzMan_SP_GenerateMemberAudit]
                    @Return,
                    5,  -- 5 for SE_AUDITID_AZ_SQL_MEMBER_REMOVE
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    N'',
                    @SidMember,
                    1,  -- 1 for member
                    N'' -- no other info
        END
          
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzRoleAssignment_Multi_SidMembers]
(
  @Return [int] output
, @RoleId [int]             -- ID Of Role Assignment
, @SidMembers varbinary (4000)
)
As
DECLARE @SaclIsOn [bit]
Set @SaclIsOn = 0

Set NoCount On

-- First parse the SidMembers string
-- Insert each one of them 
           
    Declare @AccessAtObjType [tinyint]           
	Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleId, 6, 1, @SaclIsOn output
	
	-- Require Admin Access
	if @Return <> 1 
	Begin
		if (@Return >= 2)
		Begin
		    Set @Return = -5 
		End
		goto Done
	End
DECLARE @DataLength int
DECLARE @SidLength int
DECLARE @Sid varbinary(85)
DECLARE @Pos int
DECLARE @isAdd int
SET @DataLength = DATALENGTH(@SidMembers)
            
Set @Return = 0
Set @Pos = 1

WHILE @DataLength - @Pos > 8
    BEGIN
        SET @isAdd = CAST(SUBSTRING(@SidMembers, @Pos, 1) AS int)
        SET @Pos = @Pos + 1
		
        SET @SidLength = CAST(SUBSTRING(@SidMembers, @Pos, 1) AS int)
        SET @Pos = @Pos + 1
        
        -- make sure that we the SidLength is not lying to us!
        IF @SidLength >= 12 AND @SidLength < 85 AND @DataLength - @Pos >= @SidLength - 1
            BEGIN
                SET @Sid = CAST(SUBSTRING(@SidMembers, @Pos, @SidLength) as varbinary)
                SET @Pos = @Pos + @SidLength		

			    -- if NNNN=0, then it means to delete
                IF @isAdd <> 0
                    BEGIN
                        Exec AzMan_SPI_AzRoleAssignment_Single_SidMember @Return output, @RoleId, @Sid, @SaclIsOn
					    IF @Return <> 0
                            Break
                    End	
				ELSE
				    BEGIN
                        Exec AzMan_SPD_AzRoleAssignment_Single_SidMember @Return output, @RoleId, @Sid, @SaclIsOn
                        IF @Return <> 0
                            Break
                    End
            END
        ELSE
            BEGIN
                SET @Pos = @DataLength
            END
    END

Done:
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzRoleAssignment_Single_AppMember]
(
  @Return [int] output,
  @RoleId [int]             -- ID Of Role Assignment
, @ChildID [int]
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier
DECLARE @refGroupName nvarchar(512)
DECLARE @refGroupGuid uniqueidentifier

Set @Return = 0

Set NoCount On

    INSERT INTO [AzMan_Role_AppMember] 
        (ChildID, RoleID) 
    VALUES 
        (@ChildID, @RoleId)
        
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1 
        End 
    End
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @roleName = role.Name, @roleGuid = role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] role
                WHERE role.ID = @RoleId
            SELECT @refGroupName = refGroup.Name, @refGroupGuid = refGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] refGroup
                WHERE refGroup.ID = @ChildID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    2,  -- 2 for SE_AUDITID_AZ_SQL_REFERENCE_ASSIGN
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    5,  -- 5 for application group
                    @refGroupName,
                    @refGroupGuid,
                    N'' -- no other info
        END 
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzRoleAssignment_Single_AppMember]
(
  @Return [int] output,
  @RoleId [int]             -- ID Of Role Assignment
, @ChildID [int]
, @SaclIsOn [bit]
)
As

DECLARE @RowCount INT, @Error INT
DECLARE @roleName nvarchar(512)
DECLARE @roleGuid uniqueidentifier
DECLARE @refGroupName nvarchar(512)
DECLARE @refGroupGuid uniqueidentifier
Set @Return = 0

Set NoCount On

    Delete From [AzMan_Role_AppMember] 
    where ChildID    = @ChildID and
          RoleID = @RoleId
          
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @roleName = role.Name, @roleGuid = role.ObjectGuid FROM [dbo].[AzMan_AzRoleAssignment] role
                WHERE role.ID = @RoleId
            SELECT @refGroupName = refGroup.Name, @refGroupGuid = refGroup.ObjectGuid FROM [dbo].[AzMan_AzApplicationGroup] refGroup
                WHERE refGroup.ID = @ChildID
                
            -- generate an audit
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    3,  -- 3 for SE_AUDITID_AZ_SQL_REFERENCE_REMOVE
                    6,  -- 6 for role
                    @roleName,
                    @roleGuid,
                    5,  -- 5 for application group
                    @refGroupName,
                    @refGroupGuid,
                    N'' -- no other info
        END
        
Set NoCount Off

Return(@Return)

GO


Create Procedure [AzMan_SPI_AzRoleAssignment_Multi_AppMembers]
(
  @Return [int] output
, @RoleId [int]             -- ID Of Role Assignment
, @SepChar [char] (1) = "|"
, @AppMemberIDs [nvarchar] (4000)
)
As

    Declare @AccessAtObjType [tinyint]
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0

    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleId, 6, 1, @SaclIsOn output
    
    -- Require Admin Access
    if @Return <> 1 
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End


Set @Return = 0
DECLARE @equal char
DECLARE @addOrDelete nvarchar(10)
DECLARE @isAdd int
SET @equal = '='

Set NoCount On

-- First parse the SidMembers string
-- Insert each one of them 

DECLARE @AppMember varchar(100), @Pos int
DECLARE @iAppMemberID int


    SET @AppMemberIDs = LTRIM(RTRIM(@AppMemberIDs))+ @SepChar
    
    SET @Pos = CHARINDEX(@equal, @AppMemberIDs, 1)

    IF REPLACE(@AppMemberIDs, @SepChar, '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @AppMember = LTRIM(RTRIM(LEFT(@AppMemberIDs, @Pos - 1)))
            Set @iAppMemberID = CAST(@AppMember as int)
            
            -- remove the left part (the id)
            SET @AppMemberIDs = RIGHT(@AppMemberIDs, LEN(@AppMemberIDs) - @Pos)
            
            -- now move to the pipe separator
            SET @Pos = CHARINDEX(@SepChar, @AppMemberIDs, 1)
            SET @addOrDelete = LTRIM(RTRIM(LEFT(@AppMemberIDs, @Pos - 1)))
            
            Set @isAdd = CAST(@addOrDelete as int)
            
            IF @iAppMemberID > 0
            BEGIN
                IF @isAdd <> 0
                BEGIN
                    Exec AzMan_SPI_AzRoleAssignment_Single_AppMember @Return output, @RoleId, @iAppMemberID, @SaclIsOn
                    if @Return <> 0
                    Begin
                        Break
                    End
                End
                ELSE
                BEGIN
                    Exec AzMan_SPD_AzRoleAssignment_Single_AppMember @Return output, @RoleId, @iAppMemberID, @SaclIsOn 
                    if @Return <> 0
                    Begin
                        Break
                    End
                End
            END
            SET @AppMemberIDs = RIGHT(@AppMemberIDs, LEN(@AppMemberIDs) - @Pos)
            SET @Pos = CHARINDEX(@equal, @AppMemberIDs, 1)
        END
    END 

Done:
Set NoCount Off

Return(@Return)

GO


Create Procedure [spDrop_AzMan_Table]
as
    drop table [AzMan_Role_To_Operation_Link]
    drop table [AzMan_Role_To_Task_Link]
    drop table [AzMan_Task_To_Task_Link]
    drop table [AzMan_Task_To_Operation_Link]
    drop table [AzMan_AzTask]
    drop table [AzMan_AzOperation]
    drop table [AzMan_LDAPQuery]
    drop table [AzMan_BizRule]
    drop table [AzMan_Group_SIDMember]
    drop table [AzMan_Group_AppMember]
    drop table [AzMan_AzApplicationGroup]
    drop table [AzMan_Role_SIDMember]
    drop table [AzMan_Role_AppMember]
    drop table [AzMan_AzRoleAssignment]
    drop table [AzMan_AzScope_Name] 
    drop table [AzMan_AzScope]
    drop table [AzMan_AzApplication]
    drop table [AzMan_AzAuthorizationStore]
Return(0)
GO 

----------------------------Select queries----------------------------------------------


Create Procedure [AzMan_SPS_Get_AzAuthorizationStoreByName]
-- Retrieve specific records from the [AzMan_AzAuthorizationStore] table depending on the input parameters you supply.
(
    @Return [int] output,
    @Name [nvarchar] (512)
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    DEclare @ID INT

    select @ID=ID from [AzMan_AzAuthorizationStore] where Name = @Name

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

    if @Error <> 0 
    Begin
        Set @Return = @Error
    End 
    else
    Begin 
        If @RowCount = 0
        Begin
            Set @Return = -1168 --Record not found
        End
    End     

    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 0, 0, @SaclIsOn output

    if @Return >= 1 
    Begin
            
        Select
             [ID]
            ,[DomainTimeout]
            ,[ScriptEngineTimeout]
            ,[MaxScriptEngines]
            ,[ApplyStoreSacl]
            ,[GenerateAudits]
            ,[MajorVersion]
            ,[MinorVersion]
            ,[ObjectGuid]            
            ,[TargetMachine]            
            ,[Description]
            ,[ApplicationData]
            --,[ChildUpdateTimeStamp]
        From [AzMan_AzAuthorizationStore]
        where Name = @Name 
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
        
    End     
End

Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzApplications]
(
     @Return [int] output,
     @StoreID int
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@StoreID, 0, 0, @SaclIsOn output
    
    if @Return <= 0
        goto Done
    Else if @Return = 1 or @Return = 2
    Begin
        Select
        [ID], 
        [ObjectGuid] ,
        IsNull([ApplyStoreSacl], 0),
        IsNull([GenerateAudits], 0), 
        IsNull([AuthzInterfaceClsId], 0), 
        IsNull([ApplicationVersion], N''),
        [Name],
        IsNull([Description], N''),                
        [ApplicationData]
        --[ChildUpdateTimeStamp] 
        From [AzMan_AzApplication]
        where StoreID = @StoreID  

        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End
    Else if @Return = 3 -- Delegated user
    Begin

        -- Get all the child app where the current user has access
        Select
            [ID], 
            [ObjectGuid] ,
            [ApplyStoreSacl],
            [GenerateAudits], 
            [AuthzInterfaceClsId], 
            [ApplicationVersion],
            [Name],            
            [Description],
            [ApplicationData]
            --[ChildUpdateTimeStamp] 
        From [AzMan_AzApplication]
        where StoreID = @StoreID  and
            ID in ( 
                select ObjectID from [dbo].[Azman_SQLRole]
                where [dbo].[Azman_SQLRole].[ObjectType] = 1 and 
                        is_member([dbo].[Azman_SQLRole].[SQLRoleName]) = 1 ) 
    
    
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    
    End 
        
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Get_AzApplication]
(
     @Return [int] output,
     @ID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 1, 0, @SaclIsOn output

    if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
            [StoreID],
            [ApplyStoreSacl],
            [GenerateAudits], 
            [AuthzInterfaceClsId], 
            [ObjectGuid],
            [ApplicationVersion],
            [Name],
            [Description],
            [ApplicationData]
        From [AzMan_AzApplication]
        where ID = @ID 
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End     
        
End

Return(@Return)

GO


Create Procedure [AzMan_SPS_Enum_AzScope]
(
     @Return [int] output,
     @AppID int
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the App level
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@AppID, 1, 0, @SaclIsOn output
    
    if @Return <= 0
        goto Done
    Else if @Return = 1 or @Return = 2
    Begin
        Select
            [ID], 
            [NameLen],
            IsNull([HasSpecificUsers], 0) AS HasSpecificUsers,
            [NameHash],
            [ObjectGuid] ,
            [Description],
            [Name],
            [ApplicationData]
        From [AzMan_AzScope]
        where AppID = @AppID  
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    end
    Else if @Return = 3 -- Delegated user
    Begin
    
        Select
            [ID], 
            [NameLen],
            IsNull([HasSpecificUsers], 0) AS HasSpecificUsers,
            [NameHash],
            [ObjectGuid] ,
            [Description],
            [Name],            
            [ApplicationData]
        From [AzMan_AzScope]
        where AppID = @AppID  and 
            ID in ( 
                select ObjectID from [dbo].[Azman_SQLRole]
                where [dbo].[Azman_SQLRole].[ObjectType] = 4 and 
                        is_member([dbo].[Azman_SQLRole].[SQLRoleName]) = 1 ) 
    
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    End
    
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzApplicationGroup]
(
     @Return   int output,
     @ParentID int
    ,@ParentType tinyint
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the parent
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentID, @ParentType, 0, @SaclIsOn output
    
    if @Return <= 0
        goto Done
    -- The user either has admin, reader or delegated uset at the parent
    -- So he can see the groups
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
    
        if @ParentType = 0
        Begin
            Select
                [ID], 
                [GroupType]  ,
                [ObjectGuid] ,
                [Name]       ,

                ISNULL ( [Description] , N'' ) 
            From [AzMan_AzApplicationGroup] 
            where (StoreID = @ParentID  ) and ParentType = @ParentType

        End
        else if @ParentType = 1
        Begin
            Select
                [ID], 
                [GroupType], 
                [ObjectGuid],
                [Name],
                ISNULL ( [Description] , N'' ) 
            From [AzMan_AzApplicationGroup] 
            where (AppID = @ParentID  ) and ParentType = @ParentType

        End         
        else if @ParentType = 4
        Begin
            Select
                [ID], 
                [GroupType],
                [ObjectGuid],
                [Name],
                ISNULL ( [Description] , N'' ) 
            From [AzMan_AzApplicationGroup] 
            where (ScopeID = @ParentID  ) and ParentType = @ParentType
        End         
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     

End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzTask]
(
     @Return   int output,  
     @ParentID int
    ,@ParentType tinyint
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the parent
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentID, @ParentType, 0, @SaclIsOn output
    
    if @Return <= 0
        goto Done
    -- The user either has admin, reader or delegated uset at the parent
    -- So he can see the Task
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        if @ParentType = 1
        Begin
            Select
                [ID], 
                [IsRoleDefinition],                
                [ObjectGuid] ,
                [Name]       ,
                [Description],
                [ApplicationData]
            From [AzMan_AzTask] 
            where AppID = @ParentID  
        End else if @ParentType = 4
        Begin
            Select
                [ID], 
                [IsRoleDefinition],                
                [ObjectGuid] ,
                [Name]       ,
                [Description],
                [ApplicationData]
            From [AzMan_AzTask] 
            where ScopeID = @ParentID  
        End else 
        Begin
            Set @Return = -1
            goto Done
        End
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzTask_Operations]
(
     @Return   int output,
     @TaskID int
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the Task
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@TaskID, 3, 0, @SaclIsOn output
    
    if @Return <= 0
        goto Done
    
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see the task
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
        Select
            [ObjectGuid]
        From [AzMan_Task_To_Operation_Link] INNER JOIN AzMan_AzOperation ON [AzMan_Task_To_Operation_Link].[OperationID] = AzMan_AzOperation.ID
        where TaskID = @TaskID  
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzTask_Tasks]
(
     @Return   int output,
     @TaskID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the Task
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@TaskID, 3, 0, @SaclIsOn output
    
    if @Return <= 0
        goto Done
    
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see the task
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
            [ObjectGuid]
        From [AzMan_Task_To_Task_Link] INNER JOIN AzMan_AzTask ON [AzMan_Task_To_Task_Link].[ChildID] = AzMan_AzTask.ID
        where [AzMan_Task_To_Task_Link].[TaskID] = @TaskID  
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     
End

Done:
Return(@Return)

GO


Create Procedure [AzMan_SPS_Enum_AzRoleAssignment_Tasks]
(
     @Return   int output,
     @RoleID int
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the Role 
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleID, 6, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    
    -- The user either has admin, reader or delegated uset at the parent
    -- So he can see the Role
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
        [ObjectGuid]
        From [AzMan_Role_To_Task_Link] INNER JOIN AzMan_AzTask ON [TaskID] = AzMan_AzTask.ID
        where [RoleID]  = @RoleID  
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     

End

Done:
Return (@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzRoleAssignment_Operations]
(
     @Return   int output,
     @RoleID int
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the Role 
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleID, 6, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see the role
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
        Select
            [ObjectGuid]
        From [AzMan_Role_To_Operation_Link] 
        INNER JOIN AzMan_AzOperation ON [AzMan_Role_To_Operation_Link].OperationID = AzMan_AzOperation.ID
        where [RoleID]  = @RoleID  
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     
End

Done:
Return(@Return)


GO

Create Procedure [AzMan_SPS_Enum_AzRoleAssignment]
(
     @Return   int output,
     @ParentID int
    ,@ParentType tinyint
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the parent
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ParentID, @ParentType, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
        if @ParentType = 1
        Begin
            Select
                [ID], 
                [ObjectGuid] ,
                [Name],
                [Description],
                [ApplicationData]
            From [AzMan_AzRoleAssignment] 
            where AppID = @ParentID  and ParentType = @ParentType
        End
        else if @ParentType = 4
        Begin
            Select
                [ID], 
                [ObjectGuid] ,
                [Name],
                [Description],
                [ApplicationData]
            From [AzMan_AzRoleAssignment] 
            where ScopeID = @ParentID  and ParentType = @ParentType
        End 
        else
        Begin
            Set @Return = -1
            goto Done
        End
                
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    End     
    
End

Done:
Return(@Return)

GO

Create Procedure AzMan_SPS_Get_AzApplicationGroup
(
     @Return   int output,
     @ID int
)
as 
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
        Begin
            goto Done
        End
    Else if @Return = 1 or @Return = 2 or @Return = 3
        BEGIN
            Begin
                Select
                    [GroupType],
                    [ObjectGuid] ,
                    [Name],
                    IsNull([Description], N'')
                From [AzMan_AzApplicationGroup] 
                where [ID] = @ID

            End 
                
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            if @Error <> 0 
                Set @Return = @Error
            else
                Set @Return = 0
        END

Done:
    Return(@Return)

GO

Create Procedure [AzMan_SPS_Get_AzApplicationGroup_BizruleInfo]
(
     @Return   int output,
     @ID int
)
as 
Begin

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
        Select 
            [BizRuleLanguage],
            [BizRuleImportedPath] ,
            [BizRule]
        From 
            [AzMan_BizRule]
        where ParentId = @ID  and  ParentType = 5 and ID in 
         ( Select [BizRuleID] From [AzMan_BizRule_To_Group] where [GroupID] = @ID)
           
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Get_AzTask_BizruleInfo]
(
     @Return   int output,
     @ID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 3, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Set @Return = 0

        Select 
            [BizRuleLanguage],         
            [BizRuleImportedPath] ,
            [BizRule]
        From 
            [AzMan_BizRule]
        where ParentId = @ID  and  ParentType = 3 and ID in 
         ( Select [BizRuleID] From [AzMan_BizRule_To_Task] where [TaskID] = @ID)
           
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0

            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End     
End

Done:
Return(@Return)

GO


Create Procedure [AzMan_SPS_Get_AzApplicationGroup_LDAPQuery]
(
     @Return   int output,
     @GroupID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Set @Return = 0
        Select 
            [LdapQuery]
        From 
            [AzMan_LDAPQuery]
        where GroupID = @GroupID   and ID  in 
            ( Select [LdapQueryID] From [AzMan_AzApplicationGroup] where ID = @GroupID  )
                
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0

            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzApplicationGroup_SIDMembers]
(
     @Return   int output,
     @GroupID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the Group
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
        
        Select [MemberSID]
        From [AzMan_Group_SIDMember]
        where GroupID = @GroupID  and [Member] = 1
        
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            if @Error <> 0 
            Begin
                Set @Return = @Error
            End
            else
            Begin
                Set @Return = 0
            End
        
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzApplicationGroup_SIDNonMembers]
(
     @Return   int output,
     @GroupID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]

    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the Group
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select [MemberSID]
        From [AzMan_Group_SIDMember]
        where GroupID = @GroupID  and [Member] = 0
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
        
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzApplicationGroup_AppMembers]
(
     @Return   int output,
     @GroupID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the Group
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select [ObjectGuid]
        From AzMan_AzApplicationGroup 
        where ID in ( Select ChildID from AzMan_Group_AppMember where GroupID = @GroupID and [Member] = 1 )     
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    End 
        
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzApplicationGroup_AppNonMembers]
(
     @Return   int output,
     @GroupID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@GroupID, 5, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the Group
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin
        Select [ObjectGuid]
        From AzMan_AzApplicationGroup 
        where ID in ( Select ChildID from AzMan_Group_AppMember where GroupID = @GroupID and [Member] = 0 )     

            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
            if @Error <> 0 
            Begin
                Set @Return = @Error
            End
            else
            Begin
                Set @Return = 0
            End
    End 
        
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzRoleAssignment_SIDMembers]
(
     @Return   int output,
     @RoleID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleID, 6, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the Role
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select [MemberSID]
        From [AzMan_Role_SIDMember]
        where RoleID = @RoleID

        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    End 
        
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzRoleAssignment_AppMembers]
(
     @Return   int output,
     @RoleID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@RoleID, 6, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the Role
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select [ObjectGuid]
        From AzMan_AzApplicationGroup where 
        ID IN ( select ChildID from [AzMan_Role_AppMember] where RoleID = @RoleID)
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    End 
End

Done:
Return(@Return)

go

Create Procedure [AzMan_SPS_Get_AzScope]
(
     @Return   int output,
     @ScopeID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ScopeID, 4, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the App
    -- So he can see this object 
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
        [AppID],
        [NameLen],
        IsNull([HasSpecificUsers], 0) AS HasSpecificUsers,
        [NameHash],
        [ObjectGuid] ,
        [Description],
        [Name],
        [ApplicationData]
        From [AzMan_AzScope]
        where ID = @ScopeID 
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End     
        
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Enum_AzOperation]
(
     @Return   int output,
     @AppID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at the Application
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@AppID, 1, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the App
    -- So he can see the operations
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
        [ID], 
        [OperationID],
        [ObjectGuid] ,
        [Name],
        [Description],
        [ApplicationData]
        From [AzMan_AzOperation]
        where AppID = @AppID  

        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
        if @Error <> 0 
        Begin
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = 0
        End
    End     
        
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Get_AzOperation]
(
     @Return   int output,
     @ID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at object 
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 2, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see the operation
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
        [OperationID],        
        [ObjectGuid] ,
        [Name]       ,
        [Description],
        [ApplicationData]
        From [AzMan_AzOperation]
        where ID = @ID
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End
End

Done:
Return (@Return)

GO

Create Procedure [AzMan_SPS_Get_AzTask]
(
     @Return   int output,
     @ID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at object 
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 3, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see the object
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
            [IsRoleDefinition],
            [ObjectGuid] ,
            [Name],
            [Description],
            [ApplicationData]
        From [AzMan_AzTask]
        where ID = @ID
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
        
    End     
End

Done:
Return(@Return)

GO

Create Procedure [AzMan_SPS_Get_AzRoleAssignment]
(
     @Return   int output,
     @ID int
)
as 
Begin
    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- we don't care about SACL (0 for the second last parameter)
    -- Check access at object 
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 6, 0, @SaclIsOn output
    
    if @Return <= 0
    Begin
        goto Done
    End
    -- The user either has admin, reader or delegated user at the parent
    -- So he can see the object
    Else if @Return = 1 or @Return = 2 or @Return = 3
    Begin

        Select
        [ObjectGuid] ,
        [Name]       ,
        [Description],
        [ApplicationData]
        From [AzMan_AzRoleAssignment]
        where ID = @ID
        
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  

        if @Error <> 0 
        Begin
            Set @Return = @Error
        End 
        else
        Begin 
            If @RowCount = 0
            Begin
                Set @Return = -1168 --Record not found
            End
        End     
    End
    
End

Done:
Return (@Return)


GO


-- Delete Queries

Create Procedure [AzMan_SPD_AzOperation]

-- Delete a specific record from table [AzMan_AzOperation]

(
 @Return   int output,
 @ID [int] 
,@AppId [int] = Null 
)

As

Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier

    DECLARE @childName nvarchar(512)
    DECLARE @childGuid uniqueidentifier
    Declare @AccessAtObjType [tinyint]
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 2, 1, @SaclIsOn output
    
    if @Return <> 1
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    Set @Return = 0

    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @parentName = App.Name, @parentGuid = App.ObjectGuid FROM [dbo].[AzMan_AzApplication] App
                    WHERE App.ID = @AppId
            SELECT @childName = Op.Name, @childGuid = Op.ObjectGuid FROM [dbo].[AzMan_AzOperation] Op
                    WHERE Op.ID = @ID
        END
            
    Delete From [dbo].[AzMan_AzOperation]

    Where
        ((@ID Is Null) Or ([ID] = @ID))
    And ((@AppId Is Null) Or ([AppID] = @AppId))

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End
    End

    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                    1,  -- 1 for application
                    @parentName,
                    @parentGuid,
                    2,  -- 2 for Operation
                    @childName,
                    @childGuid,
                    N'' -- no other info
        END
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzScope]
-- Delete a specific record from table [AzMan_AzScope]

(
 @Return   int output,
 @ID [int]              
,@AppId [int] = Null 
)


As
DECLARE @parentName nvarchar(512)
DECLARE @parentGuid uniqueidentifier

DECLARE @childName nvarchar(512)
DECLARE @childGuid uniqueidentifier
    
Set NoCount On

    DECLARE @RowCount INT, @Error INT
    Declare @AccessAtObjType [tinyint]
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 4, 1, @SaclIsOn output
    
    if @Return <> 1
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    Set @Return = 0

    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @parentName = App.Name, @parentGuid = App.ObjectGuid FROM [dbo].[AzMan_AzApplication] App
                    WHERE App.ID = @AppId
            SELECT @childName = scope.Name, @childGuid = scope.ObjectGuid FROM [dbo].[AzMan_AzScope] scope
                    WHERE scope.ID = @ID
        END
            
    Delete From [dbo].[AzMan_AzScope]

    Where
        ((@ID Is Null) Or ([ID] = @ID))
    And ((@AppId Is Null) Or ([AppID] = @AppId))
    
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End

    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                @Return,
                1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                1,  -- 1 for application
                @parentName,
                @parentGuid,
                4,  -- 4 for scope
                @childName,
                @childGuid,
                N'' -- no other info
        END

Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzApplication]

-- Delete a specific record from table [AzMan_AzApplication]

(
 @Return   int output,
 @ID [int] -- for [AzMan_AzApplication].[ID] column
,@StoreId [int] = Null 
)

As
    Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier

    DECLARE @childName nvarchar(512)
    DECLARE @childGuid uniqueidentifier
    Declare @AccessAtObjType [tinyint]    
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 1, 1, @SaclIsOn output
    
    if @Return <> 1
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
    Set @Return = 0

    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @parentName = store.Name, @parentGuid = store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] store
                    WHERE store.ID = @StoreId
            SELECT @childName = app.Name, @childGuid = app.ObjectGuid FROM [dbo].[AzMan_AzApplication] app
                    WHERE app.ID = @ID
        END
        
    Delete From [dbo].[AzMan_AzApplication]

    Where
        ((@ID Is Null) Or ([ID] = @ID))
    And ((@StoreId Is Null) Or ([StoreID] = @StoreId))

    
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End
    
    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                    0,  -- 0 for store
                    @parentName,
                    @parentGuid,
                    1,  -- 1 for application
                    @childName,
                    @childGuid,
                    N'' -- no other info
        END
Done:
    Set NoCount Off
            
    Return(@Return)

GO

Create Procedure [AzMan_SPD_AzAuthorizationStore]
-- Delete a specific record from table [AzMan_AzAuthorizationStore]

(
 @Return   int output,
 @ID [int]              
)

As

Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier
    Declare @AccessAtObjType [tinyint]    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 0, 1, @SaclIsOn output
    
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @parentName = store.Name, @parentGuid = store.ObjectGuid FROM [dbo].[AzMan_AzAuthorizationStore] store
                    WHERE store.ID = @ID
        END
    
    if @Return <> 1
        Begin
            if (@Return >= 2)
                Set @Return = -5
        End
    ELSE
        Begin
            Set @Return = 0
            Delete From [dbo].[AzMan_AzAuthorizationStore]
                Where ((@ID Is Null) Or ([ID] = @ID))
        End

    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                @Return,
                1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                0,  -- 0 for store
                @parentName,
                @parentGuid,
                0,  -- 0 for store
                @parentName,
                @parentGuid,
                N'' -- no other info
        END
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzApplicationGroup]
-- Delete a specific record from table [AzMan_AzApplicationGroup]
(
 @Return   int output,
 @ID [int] 
,@ParentId [int] = Null 
)

As

    Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier

    DECLARE @childName nvarchar(512)
    DECLARE @childGuid uniqueidentifier
    DECLARE @ParentType tinyint
    Declare @AccessAtObjType [tinyint]    
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 5, 1, @SaclIsOn output
    
    if @Return <> 1
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End

    Set @Return = 0

    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ParentType = appGroup.ParentType, 
                    @childName = appGroup.Name, 
                    @childGuid = appGroup.ObjectGuid 
                    FROM [dbo].[AzMan_AzApplicationGroup] appGroup
                    WHERE appGroup.ID = @ID
            IF @ParentType = 0  -- store
                SELECT  @parentName = store.Name, 
                        @parentGuid = store.ObjectGuid 
                        FROM [dbo].[AzMan_AzAuthorizationStore] store
                        WHERE store.ID = @ParentId
            ELSE IF @ParentType = 1  -- app
                SELECT  @parentName = app.Name, 
                        @parentGuid = app.ObjectGuid 
                        FROM [dbo].[AzMan_AzApplication] app
                        WHERE app.ID = @ParentId
            ELSE IF @ParentType = 4  -- scope
                SELECT  @parentName = scope.Name, 
                        @parentGuid = scope.ObjectGuid 
                        FROM [dbo].[AzMan_AzScope] scope
                        WHERE scope.ID = @ParentId
        END
    Delete From [dbo].[AzMan_AzApplicationGroup]
    Where
        ([ID] = @ID)

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End

    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                    @ParentType,
                    @parentName,
                    @parentGuid,
                    5,  -- 5 for group
                    @childName,
                    @childGuid,
                    N'' -- no other info
        END
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzTask]
-- Delete a specific record from table [AzMan_AzTask]
(
 @Return   int output,
 @ID [int] 
,@ParentId [int] = Null 
)

As
    Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier

    DECLARE @childName nvarchar(512)
    DECLARE @childGuid uniqueidentifier
    DECLARE @ParentType tinyint
    Declare @AccessAtObjType [tinyint]    
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 3, 1, @SaclIsOn output
    
    if @Return <> 1
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
    
    Set @Return = 0 
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ParentType = task.ParentType, 
                    @childName = task.Name, 
                    @childGuid = task.ObjectGuid 
                    FROM [dbo].[AzMan_AzTask] task
                    WHERE task.ID = @ID
            IF @ParentType = 1  -- app
                SELECT  @parentName = app.Name, 
                        @parentGuid = app.ObjectGuid 
                        FROM [dbo].[AzMan_AzApplication] app
                        WHERE app.ID = @ParentId
            ELSE IF @ParentType = 4  -- scope
                SELECT  @parentName = scope.Name, 
                        @parentGuid = scope.ObjectGuid 
                        FROM [dbo].[AzMan_AzScope] scope
                        WHERE scope.ID = @ParentId
        END
        
    Delete From [dbo].[AzMan_AzTask]

    Where ([ID] = @ID)

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End
    
    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                    @ParentType,
                    @parentName,
                    @parentGuid,
                    3,  -- 3 for task
                    @childName,
                    @childGuid,
                    N'' -- no other info
        END
Done:
Set NoCount Off

Return(@Return)

GO

Create Procedure [AzMan_SPD_AzRoleAssignment]
-- Delete a specific record from table 
(
 @Return   int output,
 @ID [int] 
,@ParentId [int] = Null 
)
As

    Set NoCount On

    DECLARE @RowCount INT, @Error INT
    DECLARE @parentName nvarchar(512)
    DECLARE @parentGuid uniqueidentifier

    DECLARE @childName nvarchar(512)
    DECLARE @childGuid uniqueidentifier
    DECLARE @ParentType tinyint
    Declare @AccessAtObjType [tinyint]    
    
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    -- Check access at this object
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, 6, 1, @SaclIsOn output
    
    if @Return <> 1
    Begin
        if (@Return >= 2)
        Begin
            Set @Return = -5 
        End
        goto Done
    End
    
    Set @Return = 0 
    -- get info for auditing
    IF @SaclIsOn = 1
        BEGIN
            SELECT @ParentType = role.ParentType, 
                    @childName = role.Name, 
                    @childGuid = role.ObjectGuid 
                    FROM [dbo].[AzMan_AzRoleAssignment] role
                    WHERE role.ID = @ID
            IF @ParentType = 1  -- app
                SELECT  @parentName = app.Name, 
                        @parentGuid = app.ObjectGuid 
                        FROM [dbo].[AzMan_AzApplication] app
                        WHERE app.ID = @ParentId
            ELSE IF @ParentType = 4  -- scope
                SELECT  @parentName = scope.Name, 
                        @parentGuid = scope.ObjectGuid 
                        FROM [dbo].[AzMan_AzScope] scope
                        WHERE scope.ID = @ParentId
        END
        
    Delete From [dbo].[AzMan_AzRoleAssignment]
    Where ([ID] = @ID)

    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT  
    If @RowCount <> 1
    Begin
        if @Error <> 0 
        Begin   
            Set @Return = @Error
        End
        else
        Begin
            Set @Return = -1168 --Record not found
        End 
    End
    
    -- generate an audit
    IF @SaclIsOn = 1
        BEGIN
            EXEC [AzMan_SP_GenerateObjectAudit]
                    @Return,
                    1,  -- 1 for SE_AUDITID_AZ_SQL_OBJECT_DELETE
                    @ParentType,
                    @parentName,
                    @parentGuid,
                    6,  -- 6 for role
                    @childName,
                    @childGuid,
                    N'' -- no other info
        END
Done:
Set NoCount Off

Return(@Return)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzAuthorizationStoreUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzAuthorizationStoreUpdateTimeStamp
GO

-- query the timestamps of a store
CREATE PROCEDURE AzMan_SPS_Enum_AzAuthorizationStoreUpdateTimeStamp
(
@ReturnCode int output,
@storeID int
)
AS 
SELECT @ReturnCode = 0
 
SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0), IsNull([ChildUpdateTimeStamp], 0) FROM [AzMan_AzAuthorizationStore] 
WHERE [ID] = @storeID

Return(@@RowCount)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzApplicationUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzApplicationUpdateTimeStamp
GO

-- query the timestamps of applications
CREATE PROCEDURE AzMan_SPS_Enum_AzApplicationUpdateTimeStamp
(
@ReturnCode int output,
@storeID int
)
AS 
SELECT @ReturnCode = 0

SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0), IsNull([ChildUpdateTimeStamp], 0) FROM [AzMan_AzApplication] 
WHERE [StoreID] = @storeID

Return(@@RowCount)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzScopeUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzScopeUpdateTimeStamp
GO

-- query the timestamps of scopes
CREATE PROCEDURE AzMan_SPS_Enum_AzScopeUpdateTimeStamp
(
@ReturnCode int output,
@appID int
)
AS 
SELECT @ReturnCode = 0

SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0), IsNull([ChildUpdateTimeStamp], 0) FROM [AzMan_AzScope] 
WHERE [AppID] = @appID

Return(@@RowCount)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzApplicationGroupUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzApplicationGroupUpdateTimeStamp
GO

-- query the timestamps of application groups
CREATE PROCEDURE AzMan_SPS_Enum_AzApplicationGroupUpdateTimeStamp
(
@ReturnCode int output,
@parentID int,
@parentType tinyint
)
AS 
SELECT @ReturnCode = 0

IF (@parentType = 0)
    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzApplicationGroup] 
    WHERE [StoreID] = @parentID
IF (@parentType = 1)
    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzApplicationGroup] 
    WHERE [AppID] = @parentID
IF (@parentType = 4)
    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzApplicationGroup] 
    WHERE [ScopeID] = @parentID

Return(@@RowCount)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzOperationUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzOperationUpdateTimeStamp
GO

-- query the timestamps of operations
CREATE PROCEDURE AzMan_SPS_Enum_AzOperationUpdateTimeStamp
(
@ReturnCode int output,
@appID int
)
AS 
SELECT @ReturnCode = 0

SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzOperation] 
WHERE [AppID] = @appID

Return(@@RowCount)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzRoleAssignmentUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzRoleAssignmentUpdateTimeStamp
GO

-- query the timestamps of roles
CREATE PROCEDURE AzMan_SPS_Enum_AzRoleAssignmentUpdateTimeStamp
(
@ReturnCode int output,
@parentID int,
@parentType tinyint
)
AS 
SELECT @ReturnCode = 0

IF (@parentType = 1)

    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzRoleAssignment] 
    WHERE [AppID] = @parentID

ELSE
    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzRoleAssignment] 
    WHERE [ScopeID] = @parentID

Return(@@RowCount)


GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Enum_AzTaskUpdateTimeStamp' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Enum_AzTaskUpdateTimeStamp
GO

-- query the timestamps of tasks
CREATE PROCEDURE AzMan_SPS_Enum_AzTaskUpdateTimeStamp
(
@ReturnCode int output,
@parentID int,
@parentType tinyint
)
AS 
SELECT @ReturnCode = 0

IF (@parentType = 1)

    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzTask] 
    WHERE [AppID] = @parentID

ELSE
    SELECT [ID], [ObjectGuid], IsNull([RowUpdateTimeStamp], 0) FROM [AzMan_AzTask] 
    WHERE [ScopeID] = @parentID

Return(@@RowCount)

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPD_SQLRole' and type = 'P')
    DROP PROCEDURE AzMan_SPD_SQLRole
GO

CREATE PROCEDURE AzMan_SPD_SQLRole
(
@Return             [int] output,
@SQLRoleName        [nvarchar] (64)     
)
AS
    DECLARE @Ret    [int]
    DECLARE @member [nvarchar] (64)
    
    DECLARE roleMember_cursor CURSOR
    FOR 
    (
        select u.name from sysusers u, sysusers g, sysmembers m
            where g.name = @SQLRoleName
                and g.uid = m.groupuid
                and g.issqlrole = 1
                and u.uid = m.memberuid
    )
    OPEN roleMember_cursor
    FETCH NEXT FROM roleMember_cursor INTO @member
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC @Ret = sp_droprolemember @SQLRoleName, @member
      FETCH NEXT FROM roleMember_cursor INTO @member
    END
    Close roleMember_cursor
    DEALLOCATE roleMember_cursor
    
    Set @Return = -1
    
    Exec @Ret = sp_droprole @SQLRoleName
    
    if @Ret <> 0 
    Begin
        Set @Return = -1 
    End
    Else
    Begin
        Set @Return = 0 
    End
    
    Return @Return  

GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPD_All_SQLRole_ForObject' and type = 'P')
    DROP PROCEDURE AzMan_SPD_All_SQLRole_ForObject
GO

CREATE PROCEDURE AzMan_SPD_All_SQLRole_ForObject
(
@Return     [int] output,
@ID         [int],
@ObjectType [tinyint]
)
AS
    DECLARE @uid    [int]
    DECLARE @SQLRoleName [nvarchar] (64)

    Set @Return = 0
    
    SELECT @uid = 0
    
    Select @uid=[uid], @SQLRoleName = [SQLRoleName] 
    From [dbo].[Azman_SQLRole] 
    where 
      [ObjectID]    = @ID and
      [ObjectType]  = @ObjectType and
      [RoleType]    = 1
    
    IF @uid <> 0
    Begin
        Exec AzMan_SPD_SQLRole @Return output, @SQLRoleName
    End
    
    SELECT @uid = 0 
    Select @uid=[uid], @SQLRoleName = [SQLRoleName] 
    From [dbo].[Azman_SQLRole] 
    where 
      [ObjectID]    = @ID and
      [ObjectType]  = @ObjectType and
      [RoleType]    = 2
    
    IF @uid <> 0
    Begin
        Exec AzMan_SPD_SQLRole @Return output, @SQLRoleName
    End
    
    set @uid = 0
    Select @uid=[uid], @SQLRoleName = [SQLRoleName] 
    From [dbo].[Azman_SQLRole] 
    where 
      [ObjectID]    = @ID and
      [ObjectType]  = @ObjectType and
      [RoleType]    = 3

    IF @uid <> 0
    Begin
        Exec AzMan_SPD_SQLRole @Return output, @SQLRoleName
    End

    if @Return = 0 
    Begin
        DELETE Azman_SQLRole WHERE [ObjectID] = @ID AND [ObjectType] = @ObjectType
        Set @Return = 0
    End

Done:
    Return @Return
GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPD_SQLRole_ForObject' and type = 'P')
    DROP PROCEDURE AzMan_SPD_SQLRole_ForObject
GO

CREATE PROCEDURE AzMan_SPD_SQLRole_ForObject
(
@Return     [int] output,
@ID         [int],
@ObjectType [tinyint],
@DeleteFlag [int]       -- bitwise pattern. 0x0001 for deleting only self, 0x0002 for deleting only children
)
AS
    SET NOCOUNT ON
    
    DECLARE @SQLRoleName [nvarchar] (64)
    Declare @AccessAtObjType [tinyint]    
    DECLARE @SaclIsOn [bit]
    Declare @DbName [nvarchar] (255)
    Set @SaclIsOn = 0
    
    Set @Return = -1

    -- such role deletion audit will be generated by the object deletion.
    -- so we don't care about SACL (second last parameter) here
    Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, @ObjectType, 0, @SaclIsOn output
    
    if @Return <> 1 
    Begin
        -- if the object is deleted we will ignore as we still want to delete all the roles for the object
        if @Return = -1168
        Begin
            Set @Return = 0
        End
        Else
        Begin
            if (@Return >= 2)
            Begin
                Set @Return = -5 
            End
            goto Done
        End
    End
    
    -- our deletion flag will switch to delete all because we only need
    -- to make sure that the acting container object's roles are not deleted
    -- so that the user still has access
    -- if we are instructed to delete sub-container's roles
    if (@DeleteFlag & 2) <> 0
        begin
            -- for applications or stores, we also need to delete the sub-container's roles
            DECLARE @locReturn int -- we have to press on for this action. No rollback
            IF @ObjectType = 0
                BEGIN
                    DECLARE @AppID int
                    DECLARE app_cursor CURSOR
                    FOR 
                    (
                        select App.ID FROM AzMan_AzApplication App WHERE App.StoreID = @ID
                    )
                    OPEN app_cursor
                    FETCH NEXT FROM app_cursor INTO @AppID
                    WHILE @@FETCH_STATUS = 0
                        BEGIN
                            -- now call recursively to delete the app's roles
                            EXEC AzMan_SPD_SQLRole_ForObject @locReturn output, @AppID, 1, 3
                            FETCH NEXT FROM app_cursor INTO @AppID
                        END
                    Close app_cursor
                    DEALLOCATE app_cursor
	            END
            ELSE IF @ObjectType = 1
	            BEGIN
                    DECLARE @ScopeID int
                    DECLARE scope_cursor CURSOR
                    FOR 
                    (
                        select Scope.ID FROM AzMan_AzScope Scope WHERE Scope.AppID = @ID
                    )
                    OPEN scope_cursor
                    FETCH NEXT FROM scope_cursor INTO @ScopeID
                    WHILE @@FETCH_STATUS = 0
                        BEGIN
                            -- now call recursively to delete the app's roles
                            EXEC AzMan_SPD_SQLRole_ForObject @locReturn output, @ScopeID, 4, 3
                            FETCH NEXT FROM scope_cursor INTO @ScopeID
                        END
                    Close scope_cursor
                    DEALLOCATE scope_cursor
                END
        end

    -- if we are instructed to delete the self roles
    if (@DeleteFlag & 1) <> 0
        begin
            If Is_Member('db_owner') = 1 or Is_Member('db_securityadmin') = 1
            Begin
                exec AzMan_SPD_All_SQLRole_ForObject @Return output , @ID, @ObjectType
            End
            else
            begin
                Set @DbName = db_name()
                Exec master.dbo.xp_AzManDeleteRole @Return output, @ID, @ObjectType, @DbName 
            End
        end

Done:
    SET NOCOUNT OFF
    Return @Return
    
GO

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'AzMan_SPS_Get_ObjectSecurityOption' and type = 'P')
    DROP PROCEDURE AzMan_SPS_Get_ObjectSecurityOption
GO

CREATE PROCEDURE AzMan_SPS_Get_ObjectSecurityOption
(
@Return [int] output,
@AccessAtObjType [tinyint] output,
@ID [int],
@ObjectType [tinyint],
@UserType [int] output
)
AS
    DECLARE @SaclIsOn [bit]
    Set @SaclIsOn = 0
    
    SET @Return = -5
    SET @UserType = 0   -- illegal users
    
    IF @ObjectType = 0 OR @ObjectType = 1 OR @ObjectType = 4
        BEGIN
            -- no need to know that SACL (Second last parameter)
	        Exec AzMan_SP_AccessCheck @Return output, @AccessAtObjType output,@ID, @ObjectType, 0, @SaclIsOn output
	        IF @Return >= 1 AND @Return <= 3
	            BEGIN
	                SET @UserType = @Return
	                SET @Return = 0
	            END
	    END
	    
	RETURN @Return
	
GO

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzMan_SP_Grant_Permission' and type = 'P')
    DROP PROCEDURE AzMan_SP_Grant_Permission
GO

CREATE PROCEDURE AzMan_SP_Grant_Permission
AS
    grant execute on AzMan_SP_GetSQLRole                to  public
    grant execute on AzMan_SPI_SQLRole                  to public
    --AzMan_SPI_Add_User_To_SQLRole
    grant execute on AzMan_SPI_Add_User_To_Role         to public
    --AzMan_SPD_User_From_SQLRole
    grant execute on AzMan_SPD_User_From_Role           to public
    --AzMan_SP_Get_Object_Path_For_Container
    --AzMan_SP_Get_Object_Path
    --AzMan_SP_AccessCheck_For_Container
    --AzMan_SP_AccessCheck
    grant execute on AzMan_SPS_Get_StoreIDByName              to public
    --AzMan_SP_Check_Dup_Task
    --AzMan_SP_Check_Dup_RoleAssignment
    --AzMan_SP_Check_Dup_Application
    --AzMan_SP_Check_Dup_Scope
    --AzMan_SP_Check_Dup_Operation
    --AzMan_SP_Check_Dup_Group_For_Given_Parent
    --AzMan_SP_Check_Dup_Group
    grant execute on AzMan_SPI_AzAuthorizationStore     to public
    grant execute on AzMan_SPI_AzApplication            to public
    grant execute on AzMan_SPU_AzApplication            to public
    grant execute on AzMan_SPI_AzScope                  to public
    grant execute on AzMan_SPI_AzOperation              to public
    grant execute on AzMan_SPI_AzApplicationGroup       to public   
    grant execute on AzMan_SPI_AzTask                   to public
    --AzMan_SPI_AzTask_Single_Operation
    --AzMan_SPD_AzTask_Single_Operation
    grant execute on AzMan_SPI_AzTask_Multi_Operations  to public
    --AzMan_SPI_AzRoleAssignment_Single_Operation
    --AzMan_SPD_AzRoleAssignment_Single_Operation
    grant execute on AzMan_SPI_AzRoleAssignment_Multi_Operations    to public
    --AzMan_SPI_AzTask_Single_Task
    --AzMan_SPD_AzTask_Single_Task
    grant execute on AzMan_SPI_AzTask_Multi_Tasks       to public
    --AzMan_SPI_AzRoleAssignment_Single_Task
    --AzMan_SPD_AzRoleAssignment_Single_Task
    grant execute on AzMan_SPI_AzRoleAssignment_Multi_Tasks to public
    grant execute on AzMan_SPI_AzRoleAssignment             to public
    grant execute on AzMan_SPU_AzAuthorizationStore         to public
    grant execute on AzMan_SPU_AzScope                      to public
    grant execute on AzMan_SPU_AzOperation                  to public
    grant execute on AzMan_SPU_AzApplicationGroup           to public
    grant execute on AzMan_SPU_AzTask                       to public
    grant execute on AzMan_SPU_AzRoleAssignment             to public
    grant execute on AzMan_SPIU_AzApplicationGroup_LDAPQuery to public
    grant execute on AzMan_SPIU_Bizrule                      to public
    grant execute on AzMan_SPIU_AzApplicationGroup_Bizrule   to public
    grant execute on AzMan_SPIU_AzTask_Bizrule               to public  
    --AzMan_SPI_AzApplicationGroup_Single_SidMember
    --AzMan_SPD_AzApplicationGroup_Single_SidMember
    grant execute on AzMan_SPI_AzApplicationGroup_Multi_SidMembers  to public
    grant execute on AzMan_SPI_AzApplicationGroup_Multi_SidNonMembers   to public
    --AzMan_SPI_AzApplicationGroup_Single_AppMember
    --AzMan_SPD_AzApplicationGroup_Single_AppMember
    grant execute on AzMan_SPI_AzApplicationGroup_Multi_AppMembers  to public
    grant execute on AzMan_SPI_AzApplicationGroup_Multi_AppNonMembers   to public
    --AzMan_SPI_AzRoleAssignment_Single_SidMember
    --AzMan_SPD_AzRoleAssignment_Single_SidMember
    grant execute on AzMan_SPI_AzRoleAssignment_Multi_SidMembers    to public
    -- AzMan_SPI_AzRoleAssignment_Single_AppMember
    -- AzMan_SPD_AzRoleAssignment_Single_AppMember
    grant execute on AzMan_SPI_AzRoleAssignment_Multi_AppMembers    to public
    -- spDrop_AzMan_Table
    grant execute on AzMan_SPS_Get_AzAuthorizationStoreByName       to public
    grant execute on AzMan_SPS_Enum_AzApplications                  to public
    grant execute on AzMan_SPS_Get_AzApplication                    to public
    --AzMan_SPS_Get_AzApplication_AppData
    --AzMan_SPS_Get_AzScope_AppData
    --AzMan_SPS_Get_AzAuthorizationStore_AppData
    grant execute on AzMan_SPS_Enum_AzScope                         to public
    grant execute on AzMan_SPS_Enum_AzApplicationGroup              to public
    grant execute on AzMan_SPS_Enum_AzTask                          to public
    grant execute on AzMan_SPS_Enum_AzTask_Operations               to public
    grant execute on AzMan_SPS_Enum_AzTask_Tasks                    to public   
    grant execute on AzMan_SPS_Enum_AzRoleAssignment_Tasks          to public
    grant execute on AzMan_SPS_Enum_AzRoleAssignment_Operations     to public
    grant execute on AzMan_SPS_Enum_AzRoleAssignment                to public
    grant execute on AzMan_SPS_Get_AzApplicationGroup               to public
    grant execute on AzMan_SPS_Get_AzApplicationGroup_BizruleInfo   to public
    grant execute on AzMan_SPS_Get_AzTask_BizruleInfo               to public   
    grant execute on AzMan_SPS_Get_AzApplicationGroup_LDAPQuery     to public
    grant execute on AzMan_SPS_Enum_AzApplicationGroup_SIDMembers   to public
    grant execute on AzMan_SPS_Enum_AzApplicationGroup_SIDNonMembers to public
    grant execute on AzMan_SPS_Enum_AzApplicationGroup_AppMembers   to public
    grant execute on AzMan_SPS_Enum_AzApplicationGroup_AppNonMembers    to public
    grant execute on AzMan_SPS_Enum_AzRoleAssignment_SIDMembers     to public
    grant execute on AzMan_SPS_Enum_AzRoleAssignment_AppMembers     to public
    grant execute on AzMan_SPS_Get_AzScope                          to public
    grant execute on AzMan_SPS_Enum_AzOperation                     to public
    grant execute on AzMan_SPS_Get_AzOperation                      to public
    grant execute on AzMan_SPS_Get_AzTask                           to public
    grant execute on AzMan_SPS_Get_AzRoleAssignment                 to public
    grant execute on AzMan_SPD_AzOperation                          to public
    grant execute on AzMan_SPD_AzScope                              to public
    grant execute on AzMan_SPD_AzApplication                        to public
    grant execute on AzMan_SPD_AzAuthorizationStore                 to public
    grant execute on AzMan_SPD_AzApplicationGroup                   to public
    grant execute on AzMan_SPD_AzTask                               to public
    grant execute on AzMan_SPD_AzRoleAssignment                     to public
    grant execute on AzMan_SPD_SQLRole_ForObject                    to public
    grant execute on AzMan_SPS_Enum_AzAuthorizationStoreUpdateTimeStamp to public
    grant execute on AzMan_SPS_Enum_AzApplicationUpdateTimeStamp to public
    grant execute on AzMan_SPS_Enum_AzScopeUpdateTimeStamp to public
    grant execute on AzMan_SPS_Enum_AzApplicationGroupUpdateTimeStamp to public
    grant execute on AzMan_SPS_Enum_AzOperationUpdateTimeStamp to public
    grant execute on AzMan_SPS_Enum_AzRoleAssignmentUpdateTimeStamp to public
    grant execute on AzMan_SPS_Enum_AzTaskUpdateTimeStamp to public
    grant execute on AzMan_SP_GenerateObjectAudit                   to public
    grant execute on AzMan_SP_GenerateMemberAudit                   to public
    grant execute on AzMan_SP_GenerateGenericAudit                  to public
    grant execute on AzMan_SPS_Get_ObjectSecurityOption             to public
    grant execute on AzMan_SPS_Get_DBOwners                         to public
    grant execute on AzMan_SPS_Get_Role_For_Object                  to public
    grant execute on AzMan_SP_GetRoleMemberCount                    to public
    
    grant execute on AzMan_SPI_SQLRole_From_XP                      to dbo
    grant execute on AzMan_SPD_All_SQLRole_ForObject                to dbo
    grant execute on AzMan_SPD_User_From_SQLRole_From_XP            to dbo
    grant execute on AzMan_SPI_Add_User_To_SQLRole_From_XP          to dbo
    grant execute on AzMan_SPU_SqlRoleUpdated                       to public
    grant execute on AzMan_SPI_Create_SqlRole_For_Object            to public
    
GO  


      IF not EXISTS (SELECT name FROM sysobjects
        WHERE name = 'AzGenerateAudit' and type = 'X')
    exec sp_addextendedproc 'AzGenerateAudit',                  'AzSqlExt.dll'
GO

IF not EXISTS (SELECT name FROM sysobjects
        WHERE name = 'xp_AzManAddUserToRole' and type = 'X')
    exec sp_addextendedproc 'xp_AzManAddUserToRole',            'AzSqlExt.dll'
GO

IF not EXISTS (SELECT name FROM sysobjects
        WHERE name = 'xp_AzManRemoveUserFromRole' and type = 'X')
    exec sp_addextendedproc 'xp_AzManRemoveUserFromRole',       'AzSqlExt.dll'
GO

IF not EXISTS (SELECT name FROM sysobjects
        WHERE name = 'xp_AzManDeleteRole' and type = 'X')
    exec sp_addextendedproc 'xp_AzManDeleteRole',               'AzSqlExt.dll'
GO

IF not EXISTS (SELECT name FROM sysobjects
        WHERE name = 'xp_AzManAddRole' and type = 'X')
    exec sp_addextendedproc 'xp_AzManAddRole',                  'AzSqlExt.dll'
GO

grant execute on AzGenerateAudit                to  public
grant execute on xp_AzManAddUserToRole          to  public
grant execute on xp_AzManRemoveUserFromRole     to  public
grant execute on xp_AzManDeleteRole             to  public
grant execute on xp_AzManAddRole                to  public
      placeholder     �����                    )1�B�����}����8���f���[�΂�@t~                        �   :   �      �      �              �          F I L E   R E G I S T R Y   T Y P E L I B   M U I                      M U I                 e n - U S                                                                                                                                                                                                                                                               