Re: SP accessing other db

From: Tumurbaatar S. (spam_tumur_at_magicnet.mn)
Date: 08/13/05

  • Next message: Dan Guzman: "Re: SP accessing other db" 
    Date: Sat, 13 Aug 2005 11:50:08 +0900

    The problem that the 1st db is under my control: I designed and created it.
    But the 2nd one is a db from some 3rd party application. We're adding
    an additional functionality to it, but trying to avoid modifications on the
    original db.
    Or at least a modification should be as small as possible.
    And now I see only one way: add one more SP but on the 2nd db.
    The 1st SP calls the 2nd SP, the user has only EXEC permission, no other
    rights.

    "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
    news:OB5jhA0nFHA.3256@tk2msftngp13.phx.gbl...
    > Permissions on objects referenced by the dynamic SQL statement are
    > required. SQL Server can make no assumptions about the executed statement
    > so dynamic SQL always breaks the ownership chain.
    >
    > The larger issue is why you need to use multiple databases. I would think
    > it would be possible to use a single database with an additional column to
    > logically partition the data. This would allow you to leverage ownership
    > chain security so that permissions on indirectly referenced objects are
    > not needed.
    >
    > --
    > Hope this helps.
    >
    > Dan Guzman
    > SQL Server MVP
    >
    > "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
    > news:u5cCvgynFHA.3256@TK2MSFTNGP12.phx.gbl...
    >> The user has no any roles on the 2nd db.
    >> SP generates a dynamic sql and executes it via EXEC.
    >> And permission denied.
    >>
    >> I use a dynamic sql because a name of the 2nd db
    >> passed thru parameter (e.g. @dbname + '.dbo.SomeTable').
    >> And now I wonder how to solve it. The 2nd db contains
    >> important data and I don't want to give any permissions
    >> to this user.
    >>
    >>
    >> "Uri Dimant" <urid@iscar.co.il> wrote in message
    >> news:OWUwYlxnFHA.320@TK2MSFTNGP09.phx.gbl...
    >>> Hi
    >>> Does the user have sysadmin perm or a member db_owner database role?
    >>>
    >>> Does the SP use dynamic sql inside?
    >>>
    >>> Are you unable to check it by yourself?
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
    >>> news:uysyVexnFHA.3036@TK2MSFTNGP14.phx.gbl...
    >>>>A login has access to 2 databases in SQL2K server. The 1st db has
    >>>> a stored procedure (SP) which modifies (INSERT) some table in the 2nd
    >>>> db.
    >>>> The user has EXEC permission on this SP, but no any permission
    >>>> on objects of the 2nd db (default public role).
    >>>> Is it enough? Or I should add INSERT permission on the 2nd db's table?
    >>>>
    >>>
    >>>
    >>
    >>
    >
    >

  • Next message: Dan Guzman: "Re: SP accessing other db"

    Relevant Pages

    • Re: broken ownership chains
      ... only EXEC permissions on the stored procedure, ... on all the database objects referenced within the dynamic SQL. ... > insert into OnlyNumbers values ...
      (microsoft.public.sqlserver.security)
    • Re: Cannot perform BULK INSERT even though the account is a bulkadmin
      ... When you use dynamic sql, all object permissions are checked ... BULK INSERT command seems to be setting some SET option ... > We have a stored procedure that dynamically constructs a BULK INSERT ...
      (microsoft.public.sqlserver.security)
    • Re: Sql Security ?QL
      ... Permissions on indirectly referenced objects are not checked as long as the ... Permissions to use the statementwithin the EXECUTE string are checked at ... See http://www.sommarskog.se/dynamic_sql.html for other dynamic SQL ...
      (microsoft.public.sqlserver.security)
    • Re: EXEC Permission
      ... DBA should grant you 'EXECUTE' permissions on the object ... Also,If you use dynamic sql within the stored procedure it will be forced ...
      (microsoft.public.sqlserver.programming)
    • Re: How to pass column name to a stored proc as a parameter
      ... You can use Dynamic SQL in order to do that, ... understand the security problems that this might cause you. ... ownership chain will get broken and you'll have to grant permissions on ... If you won't validate it, then SQL Injection might be used ...
      (microsoft.public.sqlserver.programming)