Execute UDF/extended stored procedure only through view?

Lets say I have a view, MyView, that calls MyUDF and/or MyExtendedProcedure.
Is there a way I can allow a user to access MyView, but stop them from
directly executing MyUDF or MyExtendedProcedure?
E.g., I'd like them to be able to do this:
select * from MyView
but stop them from doing this:
Exec MyExtendedStoredProcedure
Is this possible? Thanks for any tips.As long as the objects referenced by your view are owned by the same user,
permissions on indirectly referenced objects are not checked. This behavior
is known as ownership chaining. Beginning with SQL 2000 SP3, you also need
to also turn on the 'db chaining' database option (a.k.a. cross-database
chaining) when objects reside in different databases.
Also, the databases need to be owned by the same login in order to maintain
an unbroken chain for your dbo-owned objects in different databases. The
master database is owned by the 'sa' login so your user database needs to
also be owned by 'sa' to provide an unbroken ownership chain to your
dbo-owned extended stored procedure. You can use sp_changedbowner if
needed.
Note that 'db chaining' should be enabled in an sa-owned database when only
sysadmin role members have permissions to create dbo-owned objects. See
Cross DB Onership Chaining <adminsql.chm::/ad_config_8d7m.htm> in the Books
Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W" <neilw@.REMOVEnetlib.com> wrote in message
news:uLP5XyJ1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Lets say I have a view, MyView, that calls MyUDF and/or
> MyExtendedProcedure.
> Is there a way I can allow a user to access MyView, but stop them from
> directly executing MyUDF or MyExtendedProcedure?
> E.g., I'd like them to be able to do this:
> select * from MyView
> but stop them from doing this:
> Exec MyExtendedStoredProcedure
> Is this possible? Thanks for any tips.
>
>

Execute SQL Task: UDF not taking parameters

Hi,

I have an Execute SQL Task in my SSIS Package.
Now, this Execute SQL Task has the following query (Connection Type is OLE DB):

Code Snippet

SELECT dbo.udf_CommonDateTime_Get (GetDate(), ?) As User_Datetime

I want 2 things from this Task:
1) It should take the 2nd argument to the UDF from a variable.
2) It should store the value returned by this SELECT statement into another variable.

So, I go ahead and modify the Parameter Mapping for the Task. Here I add the Input variable name, Data type and I give the Parameter Name as 0.

I also modify the Result Set for the Task. Here, I specify the Result Name as User_Datetime and give the appropriate Variable Name.

I am getting an error here and I believe it is due to the input parameter. The UDF is not getting the 2nd argument correctly.

My questions:
1) Has the Execute SQL Task been designed to handle UDFs like this. If not, then where am I going wrong?
2) What is the work-around for this? I need to pass a parameter (variable) to the UDF.

Thanks in advance.

Regards,
B@.ns

The error message is:

Code Snippet

Execute SQL Task: Executing the query "SELECT dbo.udf_Common_DateTime_Get (GetDate(), ?)

As User_Datetime" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Set UserDateTime

As a workaround, you could you create a new variable to store your sql statement. Set the 'EvaluateAsExpression' property of the variable to 'true'. Then set the expression like this...

"SELECT dbo.udf_CommonDateTime_Get (GetDate()," + user::VariableNameHere + ") As User_Datetime"

Then in 'SourceVariable' property of the execute sql statement to 'Variable' and then choose the variable name.

|||

Hi Martin,

Thank you for the reply. At least it gives me some hope

Unfortunately, I am getting this error:

Code Snippet

The expression for variable "varQuery" failed evaluation. There was an error in the expression.

If I remove the user::VariableNameHere part, it works fine...

Any ideas?

Thanks again.

Regards,

B@.ns

|||

You replaced user::VariableNameHere with the actual name of your variable correct?

|||

Martin,

It worked!!

Thank you so much!!

I had to do this:

Code Snippet

"SELECT dbo.udf_CommonDateTime_Get(GETDATE(), " + (DT_WSTR, 1) @.[User::VariableName] + ") As UserDateTime"

The only thing worries me is that @.[User::VariableName] can be NULL.

I will have to handle that.

Thanks again.

Regard,

B@.ns