Re: Security - Stored Procedures

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

  • Next message: news.bellatlantic.net: "Re: Security - Stored Procedures" 
    Date: Sat, 6 Nov 2004 06:45:01 -0600

    This is known as ownership chaining. When the objects involved have the
    same owner, permissions are only checked on directly referenced objects.
    Permissions on indirectly referenced objects are not required.

    Beginning with SQL 2000 SP3, ownership chaining for cross-database
    references is disabled by default. You can turn this on selectively in your
    user databases by setting the 'db chaining' database option to true (EXEC
    sp_dboption 'MyDatabase', 'db chaining', true). When enabled, ownership
    chains will be unbroken as long as the object owners map to the same login.
    Note that for dbo-owned objects, this necessitates that the databases be
    owned by the same login since database ownership determines the user/login
    mapping for the dbo user. You can change database ownership using
    sp_changedbowner.

    Enable cross-database chaining in a database only if you fully trust those
    users who have permissions to create objects owned by other users (i.e.
    db-owner and db_ddladmin role members). With an sa-owned database, ensure
    only sysadmin role members can create dbo-owned objects.

    See Cross DB Ownership Chaining <adminsql.chm::/ad_config_8d7m.htm> in the
    Books Online for more information. 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"news.bellatlantic.net" <vze3vdze@verizon.net> wrote in message 
news:uLTfGX7wEHA.3288@TK2MSFTNGP14.phx.gbl...
> If I have two tables each in a different database on the same server and I
> try to reference them in a stored proc in one of the databases everything
> works fine. Then I setup the application user id (the sql login we use in
> our applications) to execute the SP. When I run it, SQL DB says it doesn't
> have permission to select from the table in the other database. Normally I
> wouldn't have to give permission to select from any table, just execute
> rights on the SP.
>
> This is a sample of the kind of SQL statement I'm talking about:
>
> SELECT a.*, b.*
> FROM Golfing.Golfers a, Employees.Users
> WHERE a.golferId = b.userid
>
> Any idea what I can do here. I don't ever want to give select rights to 
> the
> application user. It should only have rights to execute the SP.
>
> Thanks for your help.
>
>
  • Next message: news.bellatlantic.net: "Re: Security - Stored Procedures"

    Relevant Pages

    • 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 md5sums for file diffs in incremental backups
      ... > content or permissions or ownership) since the last backup. ... > filesystem against this database to determine which files have changed ... with timestamps, ownership, permissions, and the above checksums. ... Maybe Red Hat Package Manager has something equivalent for Linux. ...
      (comp.os.linux.misc)
    • 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: 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)