How can we help you today? How can we help you today?
sandu
Activity overview Badges (0) Posts (3) Comments (5)

Comments

Sort by recent activity
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 / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Problem when change function type
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 t...
0 votes
Ok thanks Waiting to hear from you Sandu / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Problem when change function type
Ok thanks Waiting to hear from you Sandu
0 votes
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 / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Problem when change function type
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 fro...
0 votes
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 / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Problem when change function type
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 ...
0 votes
hello Brian 10x Sandu / comments
  1. Community
  2. SQL Compare Previous Versions
  3. do not include plumbing for transactions
hello Brian 10x Sandu
0 votes
Total activity
10
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
2