PC World Komputer 2010 April/PCWorld0410.iso/WindowsServerTrial/server.iso/sources/install.wim/5/Windows/System32/azroles.dll/FILE/103

Text File | 2008-01-19 | 224.3 KB | 8,250 lines

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