Problem when change function type

sandu

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

Brian Donahue

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?

sandu

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

JonathanWatts

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?



Regards,

Jonathan

sandu

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

JonathanWatts

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

sandu

Ok
thanks
Waiting to hear from you
Sandu

Andras B

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

sandu

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

Andras B

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