Re: Execute UDF/extended stored procedure only through view?

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/27/04 

Date: Sat, 27 Nov 2004 16:34:12 -0600

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.
>
>
>

Relevant Pages

  • Re: Select Permission Denied On Object
    ... The main consideration with cross database chaining is that the security implications aren't obvious. ... On the other hand, if data are not sensitive and direct selects are no big deal, go with select permissions. ... > I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... If I were to implement db chaining, what would be a good generic ... table in another database? ... Permissions on indirectly referenced objects are not needed as long as the ... I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions on sp_OASetProperty
    ... this be setup with the least amount of permissions being given. ... chaining to allow limited access to the sp_OA* procs and other potentially ... dbo-owned objects in that database. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Re: Using Views for Security
    ... A security consideration when cross-database chaining enabled is that you ... must fully trust users that have permissions to create dbo-owned objects. ... in order to access objects in a database. ... It is on cross-database ownership. ...
    (microsoft.public.sqlserver.security)
  • Re: Security - Stored Procedures
    ... This is known as ownership chaining. ... Permissions on indirectly referenced objects are not required. ... owned by the same login since database ownership determines the user/login ...
    (microsoft.public.sqlserver.security)