How to block user to change UDF to a specific value

[Former Member]

I have an UDF with 3 valid value, 'N', 'Y' and 'F' in marketing document

The default value is 'N'

Could I block other users to change it to 'F' except some management users?

And when it is change to 'F', an error message is prompt 'Please contact management user'

Also, the document could not be add or update if it is set to 'F' by non management user

Could it be achieve by formatted search ??

Please help

Many Thanks!

[Former Member · Accepted solution]

Hi,

This can be done through SP_TN. Have you checked the forum regarding the SP_TransactionNotification?

FMS would only provide value instead of blocking.

Thanks,

Gordon

[Former Member · Accepted solution]

Thx Gordon

Just search SP_TN in forum and still confusing on where to use SP_TN.

Is it programmed inside SQL management Studio?

What variable name could I use to represent login user? use $[USER]?

[Former Member · Accepted solution]

Yes. It is programmed inside SQL management Studio. You may search SP_TransactionNotification on the forum instead.

[Former Member · Accepted solution]

Hi Wing,

Check This....

IF @transaction_type IN (N'A', N'U') AND  (@Object_type = N'23')
begin 

if exists(select T0.docentry from OQUT T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID  
where T0.docentry = @list_of_cols_val_tab_del and 
T0.[U_SPNT] = 'F' and T1.USERID= 1 )
begin 
SET @error = 14
SET @error_message = N'Please contact management user'
end
end

Change Table and User ID as per your requirement

Thanks,

Srujal Patel