Problem when change function type

sandusandu Posts: 20
edited June 6, 2006 5:31AM in SQL Compare Previous Versions
Hello
I have this small problem than when we change a function type from Multi-statement Table
to Inline Table Synchronization fails becase the permissions its trying to revoke insert delete update
from a function that does not have this kind of permissions
do you have any Idea besides deleting the function ,change its name or using ignore permission

thanks
Sandu

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Sandu,

    I'm not sure I understand the question but I'll tell you what I think. A UDF that returns a table can hve SELECT, ALTER, REFERENCES... permissions but not insert and update, because it's not possible to update or insert into a function. So it would be impossible for SQL Compare to try to grant or revoke these rights unless something was terribly wrong with the database.

    Your issue as I see it probably has more to do with a necessary permissions change to the tables that the UDF selects from, probably for dependency reasons. Because one of your UDFs is a multi-statement table, you introduce another table to the UDF when you do the synchronization, or take one away.

    Is this close to the issue you're having? If so, what error messages or indications do you get?
  • Hello
    Sory if I wasn't clear enough I try via example

    If I have this function :
    create function test1()
    returns table
    as
    return (select * from stores)
    select * from test1()

    then I can grant update to this function
    grant update,insert,select on test1 to guest
    and the following command is valid :
    update test1() set state='CC'
    Now if in My master DB I do the following:
    drop function test1
    create function test1()
    returns
    @retTable Table (
    [stor_id] [char] (4) ,
    [stor_name] [varchar] (40) ,
    [stor_address] [varchar] (40) ,
    [city] [varchar] (20) ,
    [state] [char] (2) ,
    [zip] [char] (5))
    as
    begin
    insert into @rettable select * from stores
    return
    end
    select * from test1()
    grant select on test1 to guest


    Now when I try to syncronize this master DB to another Sqlcompare will compare the function but will fail to revoke the permissions on the taget DB
  • Hi there,

    We have tried to reproduce this in house with your script using SQL 2000 and SQL 2005, however as you can see below. The revoke insert and update statements actually make it into the script. Can I ask what version of SQL Compare you are using? And what options you have set up?

    revokepermissions.jpg

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Hi there,
    Thats great and this is exactly the problem . did you try to run the script ?
    This us The Script I got which I belive you got it to:
    /*
    Script created by SQL Compare version 5.0.0.1622 from Red Gate Software Ltd at 5/22/2006 4:19:07 PM
    Run this script on NOGA-DB.pubs to make it the same as (local).pubs
    Please back up your database before running this script
    */
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Dropping [dbo].[test1]'
    GO
    DROP FUNCTION [dbo].[test1]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating [dbo].[test1]'
    GO
    SET ANSI_NULLS OFF
    GO
    create function test1()
    returns
    @retTable Table (
    [stor_id] [char] (4) ,
    [stor_name] [varchar] (40) ,
    [stor_address] [varchar] (40) ,
    [city] [varchar] (20) ,
    [state] [char] (2) ,
    [zip] [char] (5))
    as
    begin
    insert into @rettable select * from stores
    return
    end

    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    SET ANSI_NULLS ON
    GO
    PRINT N'Altering permissions on [dbo].[test1]'
    GO
    REVOKE INSERT ON [dbo].[test1] TO [guest]
    REVOKE UPDATE ON [dbo].[test1] TO [guest]
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    @TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO

    anfd when I tried to run the script I got

    The following error message was returned from the SQL Server:

    [4606] Granted or revoked privilege INSERT is not compatible with object.

    The following SQL command caused the error:

    REVOKE INSERT ON [dbo].[test1] TO [guest]
    REVOKE UPDATE ON [dbo].[test1] TO [guest]
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

    The following messages were returned from the SQL Server:

    [5701] Changed database context to 'pubs'.
    [5703] Changed language setting to us_english.
    [0] Dropping [dbo].[test1]
    [0] Creating [dbo].[test1]
    [0] Altering permissions on [dbo].[test1]


    And this make senece because the new function can not be granted with insert and update attributes and there for they cannot be revoked.
    Why if the function ,is recreated and not alterd in target DB, does sqlcompre care about the old permissions ? it should take into considaration only the permissions from the source DB
    I am using :5.0.0.1622
    Is there a way I can add the prtScn of this

    Thanks
    Sandu
  • Sandu,

    Sorry, clearly I didn't have enough coffee this morning. Yes this looks like an issue. I will raise it with the developers, but at the moment I cannot give any time scales as to when it will be fixed by.

    Many thanks and sorry for the misunderstanding.

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Ok
    thanks
    Waiting to hear from you
    Sandu
  • sandu wrote:
    Ok
    thanks
    Waiting to hear from you
    Sandu

    Dear Sandu,

    You are absolutely right, and this is a bug. I'm looking into it now, but meantime I would like to offer you a Red Gate T-Shirt if you give me the reason for granting both insert and update privileges on a table returning function.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Hi
    Well to be honest I do not see any reason for that either. Our Permissions are generated automatically and we gave the maximum grants on each object according to its type, which was the reason the function got this permission.
    I actually solved this by only granting select to this function, hoping that in the application no one will try to insert/update or delete from this function.

    Sandu
  • sandu wrote:
    Hi
    Well to be honest I do not see any reason for that either. Our Permissions are generated automatically and we gave the maximum grants on each object according to its type, which was the reason the function got this permission.
    I actually solved this by only granting select to this function, hoping that in the application no one will try to insert/update or delete from this function.

    Sandu

    As far as I'm aware one cannot insert data into (or update) a table returned by a user defined function directly. These functions do not allow DML triggers either. I did raise this as a bug to Microsoft, and for this you do deserve the T-Shirt :). If you send me your address and preferred size (Andras.Belokosztolszki at red-gate.com) I'll send one this week.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
This discussion has been closed.